package jsyf.oa.dao.report.newAppReport;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.alibaba.fastjson.JSONObject;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.springframework.stereotype.Repository;

import java.util.List;

@SuppressWarnings("all")
@Repository
public class NewAppReportForBRMDao extends BaseDao {
    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return null;
    }

    /*区域经理意向客户*/
    public List<JSONObject> getForPersonalIntentCustomerList(String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "t_wrt_customer.`NAME` AS  name,\n" +
                "t_wrt_customer.`ID` AS  id,\n" +
                "t_wrt_customer.TEL AS tel,\n" +
                "t_wrt_customer.SOURCE AS source,\n" +
                "t_wrt_customer.CUSTOMER_TYPE as customerType,\n" +
                "t_wrt_customer.CUSTOMER_CREDIT as customerCredit,\n" +
                "t_wrt_customer.PROVINCE_NAME AS provinceName,\n" +
                "t_wrt_customer.CITY_NAME AS cityName,\n" +
                "t_wrt_customer.COUNTY_NAME AS countyName,\n" +
                "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                "t_wrt_customer.THROW_REASON AS throwReason,\n" +
                "t_wrt_customer.INTENTION_FLAG AS intentionFlag,\n" +
                "t_wrt_customer.INTENTION_TIME AS intentionTime,\n" +
                "t_wrt_customer.EXISTING_DEVICE AS existingDevice,\n" +
                "t_wrt_customer.FUND_CONDITION AS fundCondition,\n" +
                "t_wrt_customer.OTHER_CONDITION AS otherCondition,\n" +
                "concat_ws('-',t_wrt_customer.CITY_NAME, t_wrt_customer.COUNTY_NAME) as customerlocation\n" +
                "FROM\n" +
                "t_wrt_customer\n" +
                "WHERE 1=1" + "\n");
        sql.append(" AND CREATE_USER_ID=" + "'" + userId + "'" + "\n");
        sql.append("AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n " +
                "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                "AND  t_wrt_customer.`NAME` IS NOT NULL" + "\n");
        sql.append("GROUP BY\n" +
                "t_wrt_customer.`NAME`,\n" +
                "t_wrt_customer.TEL,\n" +
                "t_wrt_customer.SOURCE ,\n" +
                "t_wrt_customer.INTENTION_MODEL ,\n" +
                "t_wrt_customer.BUY_DATE ,\n" +
                "t_wrt_customer.BUY_TYPE ,\n" +
                "t_wrt_customer.EXISTING_DEVICE,\n" +
                "t_wrt_customer.FUND_CONDITION ,\n" +
                "t_wrt_customer.OTHER_CONDITION" + "\n");
        sql.append("ORDER BY t_wrt_customer.CREATE_TIME DESC ");
        List<JSONObject> records = super.queryForJsonList(sql.toString());
        return records;
    }

    public List<JSONObject> getSignAndLeaveForToday(String userId, String dateTime) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\tsysUser.userId,\n" +
                "\tsysUser.residence,\n" +
                "\tsysUser.userName,\n" +
                "\tuserSign.signInAlwaysDistance,\n" +
                "\tuserSign.signOutAlwaysDistance,\n" +
                "\tuserSign.address,\n" +
                "\tuserSign.signOutTime,\n" +
                "\tuserSign.signOutAddress,\n" +
                "\tuserSign.latitude,\n" +
                "\tuserSign.longitude,\n" +
                "\tuserSign.signCreateTime,\n" +
                "\tuserSign.signOutUrl,\n" +
                "\tuserSign.signUrl,\n" +
                "\tuserLeave.beginTime,\n" +
                "\tuserLeave.endTime,\n" +
                "\tuserLeave.remark\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_user.ID AS userId,\n" +
                "\t\t\tt_sys_user.RESIDENCE AS residence,\n" +
                "\t\t\tt_sys_user.role_id AS roleId,\n" +
                "\t\t\tt_sys_user. NAME AS userName\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_sys_user.ID IN (" + userId + ")\n" +
                "\t) AS sysUser\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_sign_in.USER_ID AS userId,\n" +
                "\t\tt_sys_sign_in.SIGN_URL AS signUrl,\n" +
                "\t\tt_sys_sign_in.SIGN_OUT_URL AS signOutUrl,\n" +
                "\t\tt_sys_sign_in.LONGITUDE AS longitude,\n" +
                "\tSUBSTRING(t_sys_sign_in.SIGN_OUT_TIME,12,5) AS signOutTime,\n" +
                "\t\tt_sys_sign_in.ADDRESS AS address,\n" +
                "\t\tt_sys_sign_in.SIGN_OUT_ADDRESS AS signOutAddress,\n" +
                "\t\tt_sys_sign_in.SIGN_OUT_ALWAYS_DISTANCE AS signOutAlwaysDistance,\n" +
                "\t\tt_sys_sign_in.SIGN_IN_ALWAYS_DISTANCE AS signInAlwaysDistance,\n" +
                "\t\tt_sys_sign_in.LATITUDE AS latitude,\n" +
                "\t\tSUBSTRING(\n" +
                "\t\t\tt_sys_sign_in.CREATE_TIME,\n" +
                "\t\t\t12,\n" +
                "\t\t\t5\n" +
                "\t\t) AS signCreateTime\n" +
                "\tFROM\n" +
                "\t\tt_sys_sign_in\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_sys_sign_in.USER_ID IN (" + userId + ")\n" +
                "\tAND t_sys_sign_in.SIGN_DATE = '" + dateTime + "'\n" +
                ") AS userSign ON userSign.userId = sysUser.userId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tLEFT (t_sys_leave.BEGIN_TIME, 10) AS beginTime,\n" +
                "\t\tLEFT (t_sys_leave.END_TIME, 10) AS endTime,\n" +
                "\t\tt_sys_leave.USER_ID AS userId,\n" +
                "\t\tt_sys_leave.REMARK AS remark\n" +
                "\tFROM\n" +
                "\t\tt_sys_leave\n" +
                "\tWHERE\n" +
                "\t\tt_sys_leave.USER_ID IN (" + userId + ")\n" +
                "\tAND LEFT (t_sys_leave.LEAVE_DATE, 10) = '" + dateTime + "'\n" +
                "GROUP BY t_sys_leave.USER_ID\n\n" +
                ") AS userLeave ON userLeave.userId = sysUser.userId\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=sysUser.roleId\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.ID=t_sys_depart.COMPANY_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_company.DEL_FLAG=0\n" +
                "AND t_sys_depart.DEL_FLAG=0\n" +
                "AND t_sys_role.DEL_FLAG=0\n" +
                "AND t_sys_role_name.DEL_FLAG=0\n" +
                "ORDER BY t_sys_company.SORT_NO,t_sys_role_name.SORT_NO,t_sys_depart.SORT_NO,sysUser.userName,sysUser.userId");
        return super.queryForJsonList(sb.toString());
    }

    public List<JSONObject> getForPersonalCostByUserIdAndDateTime(String userId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT \n" +
                "  t_sys_user.ID AS userId,\n" +
                "  t_sys_user.`NAME` AS userName,\n" +
                "\tt_wrt_cost.CREATE_USER_ID AS createUserId,\n" +
                "\tt_wrt_cost.CREATE_TIME as createTime,\n" +
                "\tt_wrt_cost.COST_SMALL_NAME AS costSmallName,\n" +
                "\tt_wrt_cost.INVOICE_FLAG AS invoiceFlag,\n" +
                "\tt_wrt_cost.`START` AS bxState,\n" +
                "\tt_wrt_cost.REPORT_DATE AS date,\n" +
                "\tIFNULL(`t_wrt_cost`.`MONEY`, 0) AS `money`,\n" +
                "\tt_wrt_cost_detail.MILEAGE AS mileage,\n" +
                "\tt_wrt_cost_detail.IMG AS img,\n" +
                "\tt_wrt_cost_detail.LICENSE as license,\n" +
                "\tt_wrt_cost_detail.HOTEL_NAME AS hotelName,\n" +
                "\tt_wrt_cost_detail.HOTEL_TEL AS hotelTel,\n" +
                "\tt_wrt_cost_detail.COHABITATION AS cohabitation,\n" +
                "\tt_wrt_cost_detail.RESIDENCE AS residence,\n" +
                "\tt_wrt_cost_detail.`HOST` AS hostUser,\n" +
                "\tt_wrt_cost_detail.GUESTS AS guests,\n" +
                "\tt_wrt_cost_detail.GUEST_NATURE AS guestNature,\n" +
                "\tt_wrt_cost_detail.RESTAURANT_NAME AS restaurantName,\n" +
                "\tt_wrt_cost_detail.RESTAURANT_TEL AS restaurantTel,\n" +
                "\tt_wrt_cost_detail.ENTERTAINMENT_VENUE AS entertainmentVenue,\n" +
                "\tt_wrt_cost_detail.PHONE AS phone,\n" +
                "\tt_wrt_cost_detail.BEFORE_OFFICE AS beforeOffice,\n" +
                "\tt_wrt_cost_detail.AFTER_OFFICE AS afterOffice,\n" +
                "\tt_wrt_cost_detail.OFFICE_MATTERS AS officemetters,\n" +
                "\tt_wrt_cost_detail.PREDICT_MONEY AS predictMoney,\n" +
                "\tt_wrt_cost.REMARK AS remark\n" +
                "FROM t_wrt_cost\n" +
                "LEFT JOIN t_wrt_cost_detail ON t_wrt_cost.ID = t_wrt_cost_detail.FK_COST_ID\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID = t_wrt_cost.CREATE_USER_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.ID=t_sys_depart.COMPANY_ID\n" +
                "WHERE t_wrt_cost.CREATE_USER_ID in (" + userId + ")\n" +
                "AND t_sys_user.JOB_STATE!=1\n" +
                "AND t_sys_role.DEL_FLAG=0\n" +
                "AND t_sys_role_name.DEL_FLAG=0\n" +
                "AND t_sys_company.DEL_FLAG=0\n" +
                "AND t_sys_depart.DEL_FLAG=0\n" +
                "AND t_wrt_cost.REPORT_DATE = '" + dateTime + "'\n" +
                "ORDER BY t_wrt_cost.CREATE_TIME\n");
        return queryForJsonList(sql.toString());
    }

    public List<JSONObject> daQuVisit(String userIds, String dateTime, String userId, String regionId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tsysRegion.regionName,\n" +
                "\tsysUser.userId,\n" +
                "\tsysUser.userName,\n" +
                "\tvisit.num\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_region.`NAME` AS regionName,\n" +
                "\t\t\tt_sys_region.ID AS regionId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user_2_region\n" +
                "\t\tLEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID = t_sys_region.ID\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_sys_region.DEL_FLAG = 0\n");
        if (!StringUtils.isBlank(regionId)) {
            sql.append("\tAND t_sys_region.id = '" + regionId + "'\n");
        }
        sql.append("\t\tAND t_sys_region.PARENT_ID = 0\n" +
                "\t\tAND t_sys_user_2_region.USER_ID = '" + userId + "'\n" +
                "\t) AS sysRegion\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tv_sys_user.id AS userId,\n" +
                "\t\tv_sys_user.`name` AS userName,\n" +
                "\t\tv_sys_user.departName,\n" +
                "\t\tv_sys_user.companySortNo,\n" +
                "\t\tv_sys_user.departSortNo,\n" +
                "\t\tv_sys_user.roleNameLevel,\n" +
                "\t\tt_sys_user_2_region.REGION_ID AS regionId\n" +
                "\tFROM\n" +
                "\t\tt_sys_user_2_region\n" +
                "\tLEFT JOIN v_sys_user ON v_sys_user.id = t_sys_user_2_region.USER_ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND jobState != 1\n" +
                "\tAND v_sys_user.id in (" + userIds + ")\n" +
                "\tAND v_sys_user.roleName = '大区经理'\n" +
                ") AS sysUser ON sysUser.regionId = sysRegion.regionId\n" +
                "AND sysUser.departName = sysRegion.regionName\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tCOUNT(1) AS num,\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID AS userId\n" +
                "\tFROM\n" +
                "\t\tt_wrt_customer_visit\n" +
                "\tLEFT JOIN t_wrt_customer ON t_wrt_customer_visit.CUSTOMER_ID = t_wrt_customer.ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND LEFT (\n" +
                "\t\tt_wrt_customer_visit.REPORT_DATE,\n" +
                "\t\t7\n" +
                "\t) = '" + dateTime + "'\n" +
                "\tAND t_wrt_customer_visit.CREATE_USER_ID IN (" + userIds + ")\n" +
                "\tAND t_wrt_customer_visit.ENTOURAGE_ID IS NOT NULL\n" +
                "\tAND t_wrt_customer_visit.customer_id IS NOT NULL\n" +
                "\tAND t_wrt_customer_visit.VISIT_MODE = 1\n" +
                "\tGROUP BY\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID\n" +
                ") AS visit ON visit.userId = sysUser.userId\n" +
                "ORDER BY\n" +
                "\tsysUser.companySortNo,\n" +
                "\tsysUser.departSortNo,\n" +
                "\tsysUser.roleNameLevel,\n" +
                "\tsysUser.userName\n");
        return queryForJsonList(sql.toString());
    }

    public List<JSONObject> daQuVisitByUserId(String userId, String dateTime, StringBuffer condition, Integer requestPage, Integer pageSize) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT \n" +
                "t_wrt_customer.`NAME` as `customerName`\n" +
                ",t_wrt_customer_visit.CREATE_USER_ID as userId\n" +
                ",t_wrt_customer.ID as customerId\n" +
                ",t_wrt_customer.TEL as customerTel\n" +
                ",t_wrt_customer.WECHAT as customerWechat\n" +
                ",t_wrt_customer_visit.ENTOURAGE as entourageName\n" +
                ",t_wrt_customer_visit.ENTOURAGE_ID as entourageId\n" +
                ",t_wrt_customer_visit.CUSTOMER_TYPE as customerType\n" +
                ",t_wrt_customer_visit.CUSTOMER_CREDIT AS customerCredit\n" +
                ",t_wrt_customer_visit.ARRIVALS_TIME AS arrivalsTime\n" +
                ",t_wrt_customer_visit.LEAVE_TIME AS leaveTime\n" +
                ",t_wrt_customer_visit.LEAVE_IMG as leaveImg\n" +
                ",t_wrt_customer_visit.ARRIVALS_IMG AS arrivalsImg\n" +
                ",t_wrt_customer_visit.OTHER_CONDITION as otherCondition\n" +
                "FROM t_wrt_customer_visit\n" +
                "LEFT JOIN t_wrt_customer ON t_wrt_customer_visit.CUSTOMER_ID=t_wrt_customer.ID\n" +
                "WHERE 1=1\n" +
                "AND t_wrt_customer_visit.VISIT_MODE=1\n" +
                "\tAND t_wrt_customer_visit.ENTOURAGE_ID IS NOT NULL\n" +
                "AND t_wrt_customer_visit.CUSTOMER_ID IS NOT NULL\n" +
                "AND left(t_wrt_customer_visit.REPORT_DATE,7)='" + dateTime + "'\n" +
                "AND t_wrt_customer_visit.CREATE_USER_ID = '" + userId + "'\n");
        sql.append(condition);
        sql.append("ORDER BY t_wrt_customer_visit.CREATE_TIME\n\n");
        super.appendPageSql(sql, requestPage, pageSize);

        List<JSONObject> jsonList = super.queryForJsonList(sql.toString());
        return jsonList;
    }


    public Integer daQuVisitByUserIdPageCount(String userId, String dateTime, StringBuffer condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT count(1) as count from( SELECT \n" +
                "t_wrt_customer.`NAME` as `customerName`\n" +
                ",t_wrt_customer_visit.CREATE_USER_ID as userId\n" +
                ",t_wrt_customer.ID as customerId\n" +
                ",t_wrt_customer.TEL as customerTel\n" +
                ",t_wrt_customer.WECHAT as customerWechat\n" +
                ",t_wrt_customer_visit.ENTOURAGE as entourageName\n" +
                ",t_wrt_customer_visit.ENTOURAGE_ID as entourageId\n" +
                ",t_wrt_customer_visit.CUSTOMER_TYPE as customerType\n" +
                ",t_wrt_customer_visit.CUSTOMER_CREDIT AS customerCredit\n" +
                ",t_wrt_customer_visit.ARRIVALS_TIME AS arrivalsTime\n" +
                ",t_wrt_customer_visit.LEAVE_TIME AS leaveTime\n" +
                ",t_wrt_customer_visit.LEAVE_IMG as leaveImg\n" +
                ",t_wrt_customer_visit.ARRIVALS_IMG AS arrivalsImg\n" +
                ",t_wrt_customer_visit.OTHER_CONDITION as otherCondition\n" +
                "FROM t_wrt_customer_visit\n" +
                "LEFT JOIN t_wrt_customer ON t_wrt_customer_visit.CUSTOMER_ID=t_wrt_customer.ID\n" +
                "WHERE 1=1\n" +
                "AND t_wrt_customer_visit.VISIT_MODE=1\n" +
                "\tAND t_wrt_customer_visit.ENTOURAGE_ID IS NOT NULL\n" +
                "AND t_wrt_customer_visit.CUSTOMER_ID IS NOT NULL\n" +
                "AND left(t_wrt_customer_visit.REPORT_DATE,7)='" + dateTime + "'\n" +
                "AND t_wrt_customer_visit.CREATE_USER_ID = '" + userId + "'\n");
        sql.append(condition);
        sql.append("ORDER BY t_wrt_customer_visit.CREATE_TIME   ) as num\n");
        String count = super.queryForString(sql.toString(), new Object[]{});
        return Integer.parseInt(count);
    }

    public List<JSONObject> getCostListByUserId(String userIds, String dateTime) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\tsysUser.userId,\n" +
                "\tsysUser.userName,\n" +
                "\tIFNULL(sysCost.money, 0) AS money,\n" +
                "\tIFNULL(sysCost.bxMoney, 0) AS bxMoney,\n" +
                "\tIFNULL(sysCost.unBxMoney, 0) AS unBxMoney\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_user.ID AS userId,\n" +
                "\t\t\tt_sys_user.`NAME` AS userName,\n" +
                "\t\t\tt_sys_role_name.SORT_NO AS roleNameSortNo,\n" +
                "\t\t\tt_sys_depart.SORT_NO AS departSortNo,\n" +
                "\t\t\tt_sys_company.SORT_NO AS companySortNo\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user\n" +
                "\t\tLEFT JOIN t_sys_role ON t_sys_role.ID = t_sys_user.ROLE_ID\n" +
                "\t\tLEFT JOIN t_sys_depart ON t_sys_depart.ID = t_sys_role.DEPART_ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_depart.COMPANY_ID\n" +
                "\t\tLEFT JOIN t_sys_role_name ON t_sys_role_name.ID = t_sys_role.ROLE_NAME_ID\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_sys_user.id IN (" + userIds + ")\n" +
                "\t\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_role_name.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_role.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_depart.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_user.JOB_STATE != 1\n" +
                "\t) AS sysUser\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tv_wrt_cost_for_report.userId,\n" +
                "\t\tSUM(IF(bxState <> - 1, money, 0)) AS money,\n" +
                "\t\tSUM(IF(bxState = 1, money, 0)) AS bxMoney,\n" +
                "\t\tSUM(IF(bxState = 0, money, 0)) AS unBxMoney\n" +
                "\tFROM\n" +
                "\t\tv_wrt_cost_for_report\n" +
                "\tWHERE\n" +
                "\t\tLEFT (\n" +
                "\t\t\tv_wrt_cost_for_report.reportDate,\n" +
                "\t\t\t7\n" +
                "\t\t) = '" + dateTime + "'\n" +
                "\tGROUP BY\n" +
                "\t\tuserId\n" +
                ") AS sysCost ON sysUser.userId = sysCost.userId\n" +
                "ORDER BY\n" +
                "\tsysUser.companySortNo,\n" +
                "\tsysUser.roleNameSortNo,\n" +
                "\tsysUser.departSortNo,\n" +
                "\tsysUser.userName\n");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    //报表--今日数据
    public List<JSONObject> getForPersonalDayByUserIdAndDateTime(String userIds, String dateTime, Integer type, Integer status) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT customer.* FROM(\n" +
                "SELECT t_wrt_customer.`NAME` as `customerName`\n" +
                ",t_wrt_customer_visit.CREATE_USER_ID as userId\n" +
                ",t_sys_user.`NAME` as userName\n" +
                ",t_sys_role_name.SORT_NO as roleSortNo\n" +
                ",t_sys_company.SORT_NO as companySortNo\n" +
                ",t_sys_depart.SORT_NO as departSortNo\n" +
                ",t_wrt_customer.ID as customerId\n" +
                ",t_wrt_customer.TEL as customerTel\n" +
                ",t_wrt_customer.WECHAT as customerWechat\n" +
                ",t_wrt_customer_visit.ENTOURAGE as entourageName\n" +
                ",t_wrt_customer_visit.ENTOURAGE_ID as entourageId\n" +
                ",t_wrt_customer_visit.CUSTOMER_TYPE as customerType\n" +
                ",t_wrt_customer_visit.CUSTOMER_CREDIT AS customerCredit\n" +
                ",t_wrt_customer_visit.ARRIVALS_TIME AS arrivalsTime\n" +
                ",t_wrt_customer_visit.LEAVE_TIME AS leaveTime\n" +
                ",t_wrt_customer_visit.LEAVE_IMG as leaveImg\n" +
                ",t_wrt_customer_visit.ARRIVALS_IMG AS arrivalsImg\n" +
                ",t_wrt_customer_visit.OTHER_CONDITION as otherCondition\n" +
                ",substring(t_wrt_customer_visit.ARRIVALS_TIME,11,3)>=12 as status\n" +
                ",IFNULL(CEIL(CEILING(UNIX_TIMESTAMP(t_wrt_customer_visit.LEAVE_TIME)-UNIX_TIMESTAMP(t_wrt_customer_visit.ARRIVALS_TIME))/60),0) as time \n" +
                "FROM t_wrt_customer_visit\n" +
                "LEFT JOIN t_wrt_customer ON t_wrt_customer_visit.CUSTOMER_ID=t_wrt_customer.ID\n" +
                "LEFT JOIN t_sys_user ON t_wrt_customer_visit.CREATE_USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.ID=t_sys_depart.COMPANY_ID\n" +
                "WHERE 1=1\n" +
                "AND t_sys_user.JOB_STATE!=1\n" +
                "AND t_sys_role.DEL_FLAG=0\n" +
                "AND t_sys_role_name.DEL_FLAG=0\n" +
                "AND t_sys_company.DEL_FLAG=0\n" +
                "AND t_sys_depart.DEL_FLAG=0\n" +
                "AND t_wrt_customer_visit.VISIT_MODE=" + type + "\n" +
                "AND t_wrt_customer_visit.REPORT_DATE='" + dateTime + "'\n" +
                "AND t_wrt_customer_visit.CREATE_USER_ID in (" + userIds + ")) AS customer\n");
        sb.append("WHERE customer.status=" + status + "\n");
        sb.append("ORDER BY customer.companySortNo,customer.roleSortNo,customer.departSortNo,customer.userName,customer.arrivalsTime\n\n");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getTomorrowVisitToday(String userIds, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tsysUser.userName,\n" +
                "\tT_WRT_CUSTOMER_VIST_PLAN.CUSTOMER_ADDRESS AS customerAddress,\n" +
                "\tT_WRT_CUSTOMER_VIST_PLAN.ENTOURAGE AS entourage,\n" +
                "\tT_WRT_CUSTOMER_VIST_PLAN.VISIT_GOAL AS visitGoal,\n" +
                "\tT_WRT_CUSTOMER_VIST_PLAN.CUSTOMER_NAME AS customerName,\n" +
                "\tT_WRT_CUSTOMER_VIST_PLAN.COUNTY AS county,\n" +
                "\tCASE\n" +
                "WHEN t_sys_sign_in.ID IS NOT NULL THEN\n" +
                "\t1\n" +
                "WHEN t_sys_leave.ID IS NOT NULL THEN\n" +
                "\t0\n" +
                "WHEN t_sys_email.ID IS NOT NULL THEN\n" +
                "\t3\n" +
                "ELSE\n" +
                "\t2\n" +
                "END AS signState,\n" +
                "CASE WHEN t_sys_email.ID IS NOT NULL THEN\n" +
                "\t1\n" +
                "ELSE\n" +
                "0\n" +
                "END as reportState,\n"+
                " CASE\n" +
                "WHEN t_sys_sign_in.ID IS NOT NULL THEN\n" +
                "\t10\n" +
                "WHEN t_sys_leave.ID IS NOT NULL THEN\n" +
                "\t20\n" +
                "WHEN t_sys_email.ID IS NOT NULL THEN\n" +
                "\t30\n" +
                "ELSE\n" +
                "\t40\n" +
                "END signSort\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_user.ID AS userId,\n" +
                "\t\t\tt_sys_user.`NAME` AS userName,\n" +
                "\t\t\tt_sys_depart.`NAME` AS departName,\n" +
                "\t\t\tt_sys_company.`NAME` AS companyName,\n" +
                "\t\t\tt_sys_company.SORT_NO AS companySortNo,\n" +
                "\t\t\tt_sys_depart.SORT_NO AS departSortNo\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user\n" +
                "\t\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\t\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_sys_user.ID IN (" + userIds + ")\n" +
                "\t\tAND t_sys_role.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_depart.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\t) AS sysUser\n" +
                "LEFT JOIN T_WRT_CUSTOMER_VIST_PLAN ON sysUser.userId = T_WRT_CUSTOMER_VIST_PLAN.CREATE_USER_ID\n" +
                "AND T_WRT_CUSTOMER_VIST_PLAN.CREATE_USER_ID IN (" + userIds + ")\n" +
                "AND T_WRT_CUSTOMER_VIST_PLAN.REPORT_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_sign_in ON t_sys_sign_in.USER_ID = sysUser.userId\n" +
                "AND t_sys_sign_in.SIGN_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_leave ON t_sys_leave.USER_ID = sysUser.userId\n" +
                "AND t_sys_leave.LEAVE_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_email ON t_sys_email.CREATE_USER_ID = sysUser.userId\n" +
                "AND t_sys_email.REPORT_DATE = '" + dateTime + "'\n" +
                "AND t_sys_email.E_TYPE = 1\n" +
                "ORDER BY\n" +
                "\tsysUser.companySortNo,\n" +
                "\tsysUser.departSortNo,\n" +
                "\tsignSort,\n" +
                "sysUser.userId\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getFirstVisitAndSignForToday(String userId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tsysUser.userId,\n" +
                "\tsysUser.userName,\n" +
                "\tsysUser.roleNameSortNo,\n" +
                "\tt_sys_sign_in.LATITUDE AS signInLatitude,\n" +
                "\tt_sys_sign_in.LONGITUDE AS signInLongitude,\n" +
                "\tt_sys_sign_in.SIGN_IN_SPEED AS signInspeed,\n" +
                "\tt_sys_sign_in.CREATE_TIME as signTime,\n" +
                "\tt_sys_sign_in.SIGN_IN_TIME_DIFF AS signInDiff,\n" +
                "\tt_sys_sign_in.SIGN_IN_DISTANCE AS signInDistance,\n" +
                "\twrtArr.arrivalsTime,\n" +
                "\twrtArr.arrivalsLatitude,\n" +
                "\twrtArr.arrivalsLongitude\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_user.id AS userId,\n" +
                "\t\t\tt_sys_user. NAME AS userName,\n" +
                "\t\t\t\tt_sys_role_name.SORT_NO AS roleNameSortNo,\n" +
                "\t\t\tt_sys_company.SORT_NO AS companySortNo,\n" +
                "\t\t\tt_sys_depart.SORT_NO AS departSortNo\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user\n" +
                "\t\tLEFT JOIN t_sys_role ON t_sys_role.ID = t_sys_user.ROLE_ID\n" +
                "\t\tLEFT JOIN t_sys_role_name ON t_sys_role_name.ID = t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.ID=t_sys_depart.COMPANY_ID\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_sys_user.JOB_STATE != 1\n" +
                "\t\tAND t_sys_role.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_role_name.DEL_FLAG = 0\n" +
                "AND t_sys_company.DEL_FLAG=0\n" +
                "AND t_sys_depart.DEL_FLAG=0\n" +
                "\t\tAND t_sys_user.ID IN (" + userId + ")\n" +
                "\t) AS sysUser\n" +
                "LEFT JOIN t_sys_sign_in ON t_sys_sign_in.CREATE_USER_ID = sysUser.userId\n" +
                "AND t_sys_sign_in.SIGN_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID AS userId,\n" +
                "\t\tmin(\n" +
                "\t\t\tt_wrt_customer_visit.ARRIVALS_TIME\n" +
                "\t\t) AS arrivalsTime,\n" +
                "\t\tt_wrt_customer_visit.ARRIVALS_LATITUDE AS arrivalsLatitude,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_LONGITUDE AS arrivalsLongitude\n" +
                "\tFROM\n" +
                "\t\tt_wrt_customer_visit\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_wrt_customer_visit.VISIT_MODE = 1\n" +
                "\tAND t_wrt_customer_visit.REPORT_DATE = '" + dateTime + "'\n" +
                "\tGROUP BY\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID\n" +
                ") AS wrtArr ON wrtArr.userId = sysUser.userId\n" +
                "ORDER BY sysUser.companySortNo,sysUser.roleNameSortNo,sysUser.departSortNo,sysUser.userName\n\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getVisitCustomerByuserIdAndDateTime(String userIds, String dateTime, Integer type, String userId, String regionId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tsysRegion.regionName,\n" +
                "\tsysRegion.regionId,\n" +
                "\tsysUser.userId,\n" +
                "\tsysUser.userName,\n" +
                "\tifnull(visit.num,0) as num,\n" +
                "\tCASE\n" +
                "WHEN sysRegion.regionParentId = 0 THEN\n" +
                "\t0\n" +
                "ELSE\n" +
                "\t1\n" +
                "END AS regionPSortNo\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_company.SORT_NO AS companysortNo,\n" +
                "\t\t\tt_sys_user_2_region.REGION_ID AS regionId,\n" +
                "\t\t\tt_sys_region.`NAME` AS regionName,\n" +
                "\t\t\tt_sys_region.`SORT_NO` AS regionSortNo,\n" +
                "\t\t\tt_sys_region.PARENT_ID AS regionParentId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user_2_region\n" +
                "\t\tLEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID = t_sys_region.ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_region.COMPANY_ID\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_sys_company.DEL_FLAG = 0\n");
        if (!StringUtils.isBlank(regionId)) {
            sql.append("\t\tAND (\n" +
                    "\t\t\tt_sys_region.id = '" + regionId + "'\n" +
                    "\t\t\tOR t_sys_region.PARENT_ID = '" + regionId + "'\n" +
                    "\t\t)\n");
        }
        sql.append("\t\tAND t_sys_region.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_user_2_region.USER_ID = '" + userId + "'\n" +
                "\t\tGROUP BY\n" +
                "\t\t\tt_sys_user_2_region.REGION_ID\n" +
                "\t\tORDER BY\n" +
                "\t\t\tt_sys_region.SORT_NO\n" +
                "\t) AS sysRegion\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_region.ID AS regionId,\n" +
                "\t\tt_sys_region.`NAME` AS regionName,\n" +
                "\t\tt_sys_company.SORT_NO AS companysortNo,\n" +
                "\t\tt_sys_region.`SORT_NO` AS regionSortNo,\n" +
                "\t\tt_sys_depart.`SORT_NO` AS departSortNo,\n" +
                "\t\tt_sys_region.PARENT_ID AS regionParentId,\n" +
                "\t\tv_sys_user.id AS userId,\n" +
                "\t\tv_sys_user.roleNameLevel,\n" +
                "\t\tv_sys_user.`name` AS userName\n" +
                "\tFROM\n" +
                "\t\tt_sys_user_2_region\n" +
                "\tLEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID = t_sys_region.ID\n" +
                "\tLEFT JOIN v_sys_user ON v_sys_user.id = t_sys_user_2_region.USER_ID\n" +
                "\tLEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_region.COMPANY_ID\n" +
                "\tLEFT JOIN t_sys_depart ON v_sys_user.departId = t_sys_depart.ID\n" +
                "\tAND v_sys_user.departName = t_sys_region.`NAME`\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\tAND t_sys_region.DEL_FLAG = 0\n");
        if (!StringUtils.isBlank(regionId)) {
            sql.append("\t\tAND (\n" +
                    "\t\t\tt_sys_region.id = '" + regionId + "'\n" +
                    "\t\t\tOR t_sys_region.PARENT_ID = '" + regionId + "'\n" +
                    "\t\t)\n");
        }
        sql.append("\tAND t_sys_user_2_region.USER_ID IN (" + userIds + ")\n" +
                "\tAND t_sys_region.PARENT_ID = 0\n" +
                "\tAND v_sys_user.roleName IN (\n" +
                "\t\t'大区经理',\n" +
                "\t\t'区域组长'\n" +
                "\t)\n" +
                "\tUNION\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_region.ID AS regionId,\n" +
                "\t\t\tt_sys_region.`NAME` AS regionName,\n" +
                "\t\t\tt_sys_company.SORT_NO AS companysortNo,\n" +
                "\t\t\tt_sys_depart.`SORT_NO` AS departSortNo,\n" +
                "\t\t\tt_sys_region.`SORT_NO` AS regionSortNo,\n" +
                "\t\t\tt_sys_region.PARENT_ID AS regionParentId,\n" +
                "\t\t\tv_sys_user.id AS userId,\n" +
                "\t\t\tv_sys_user.roleNameLevel,\n" +
                "\t\t\tv_sys_user.`name` AS userName\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user_2_region\n" +
                "\t\tLEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID = t_sys_region.ID\n" +
                "\t\tLEFT JOIN v_sys_user ON v_sys_user.id = t_sys_user_2_region.USER_ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_region.COMPANY_ID\n" +
                "\t\tLEFT JOIN t_sys_depart ON v_sys_user.departId = t_sys_depart.ID\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_region.DEL_FLAG = 0\n");
        if (!StringUtils.isBlank(regionId)) {
            sql.append("\t\tAND (\n" +
                    "\t\t\tt_sys_region.id = '" + regionId + "'\n" +
                    "\t\t\tOR t_sys_region.PARENT_ID = '" + regionId + "'\n" +
                    "\t\t)\n");
        }
        sql.append("\t\tAND t_sys_user_2_region.USER_ID IN (" + userIds + ")\n" +
                "\t\tAND t_sys_region.PARENT_ID != 0\n" +
                "\t\tAND v_sys_user.roleName IN ('区域经理')\n" +
                ") AS sysUser ON sysUser.regionId = sysRegion.regionId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tCOUNT(1) AS num,\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID AS userId\n" +
                "\tFROM\n" +
                "\t\tt_wrt_customer_visit\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_wrt_customer_visit.CUSTOMER_ID IS NOT NULL\n" +
                "\tAND t_wrt_customer_visit.VISIT_MODE = " + type + "\n" +
                "\tAND LEFT (\n" +
                "\t\tt_wrt_customer_visit.REPORT_DATE,\n" +
                "\t\t7\n" +
                "\t) = '" + dateTime + "'\n" +
                "\tAND t_wrt_customer_visit.CREATE_USER_ID IN (" + userIds + ")\n" +
                "\tGROUP BY\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID\n" +
                ") AS visit ON visit.userId = sysUser.userId\n" +
                "GROUP BY sysUser.userId,sysRegion.regionId\n" +
                "ORDER BY\n" +
                "\tsysRegion.companysortNo,\n" +
                "\tregionPSortNo,\n" +
                "\tsysRegion.regionSortNo,\n" +
                "\tsysUser.roleNameLevel,\n" +
                "\tsysUser.departsortNo,\n" +
                "\tsysUser.userName\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getCustomerNumByuserIdAndDateTime(String userIds, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tsysUser.userName,\n" +
                "\tsysUser.userId,\n" +
                "\tt4.sumNum as sunNum,\n" +
                "\tt5.count as newNum\n" +
                "FROM\n" +
                "(SELECT t_sys_user.id as userId, \n" +
                "t_sys_user.name as userName,\n" +
                "t_sys_user.role_id as roleId\n" +
                "FROM t_sys_user\n" +
                " WHERE id in (" + userIds + ")) as sysUser\n" +
                "LEFT JOIN\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tCOUNT(sysMain.CUSTOMER_ID) AS sumNum,\n" +
                "\t\t\tsysMain.createUserId AS createUserId\n" +
                "\t\tFROM\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID AS createUserId,\n" +
                "\t\t\t\t\tt_wrt_customer_visit.CUSTOMER_ID\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tt_wrt_customer_visit\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t1 = 1\n" +
                "\t\t\t\tAND t_wrt_customer_visit.CREATE_USER_ID IN (" + userIds + ")\n" +
                "\t\t\t\tAND t_wrt_customer_visit.CUSTOMER_ID IS NOT NULL\n" +
                "\t\t\t\tGROUP BY\n" +
                "\t\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID,\n" +
                "\t\t\t\t\tt_wrt_customer_visit.CUSTOMER_ID\n" +
                "\t\t\t) AS sysMain\n" +
                "\t\tGROUP BY\n" +
                "\t\t\tsysMain.createUserId\n" +
                "\t) AS t4  on t4.createUserId=sysUser.userId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt3.createUserId,\n" +
                "\t\tCOUNT(1) AS count\n" +
                "\tFROM\n" +
                "\t\t(\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tt1.createUserId,\n" +
                "\t\t\t\tt2.customerId\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\t(\n" +
                "\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\tt_wrt_customer_visit.CUSTOMER_ID AS customerId,\n" +
                "\t\t\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID AS createUserId\n" +
                "\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\tt_wrt_customer_visit\n" +
                "\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t1 = 1\n" +
                "\t\t\t\t\tAND t_wrt_customer_visit.CREATE_USER_ID IN (" + userIds + ")\n" +
                "\t\t\t\t\tAND t_wrt_customer_visit.CUSTOMER_ID IS NOT NULL\n" +
                "\t\t\t\t\tAND left(t_wrt_customer_visit.REPORT_DATE,7) = '" + dateTime + "'\n" +
                "\t\t\t\t\tGROUP BY\n" +
                "\t\t\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID,\n" +
                "\t\t\t\t\t\tt_wrt_customer_visit.CUSTOMER_ID\n" +
                "\t\t\t\t) AS t1\n" +
                "\t\t\tLEFT JOIN (\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tt_wrt_customer_visit.CUSTOMER_ID AS customerId,\n" +
                "\t\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID AS createUserId\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tt_wrt_customer_visit\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t1 = 1\n" +
                "\t\t\t\tAND t_wrt_customer_visit.CREATE_USER_ID IN (" + userIds + ")\n" +
                "\t\t\t\tAND t_wrt_customer_visit.CUSTOMER_ID IS NOT NULL\n" +
                "\t\t\t\tAND left(t_wrt_customer_visit.REPORT_DATE,7) < '" + dateTime + "'\n" +
                "\t\t\t\tGROUP BY\n" +
                "\t\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID,\n" +
                "\t\t\t\t\tt_wrt_customer_visit.CUSTOMER_ID\n" +
                "\t\t\t) AS t2 ON t1.createUserId = t2.createUserId\n" +
                "\t\t\tAND t1.customerId = t2.customerId\n" +
                "\t\t) AS t3\n" +
                "\tWHERE\n" +
                "\t\tt3.customerId IS NULL\n" +
                "\tGROUP BY\n" +
                "\t\tt3.createUserId\n" +
                ") AS t5 ON t4.createUserId = t5.createUserId\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID = sysUser.roleId\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID = t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_depart.COMPANY_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_depart.DEL_FLAG = 0\n" +
                "AND t_sys_role.DEL_FLAG = 0\n" +
                "AND t_sys_role_name.DEL_FLAG = 0\n" +
                "AND t_sys_company.DEL_FLAG = 0\n" +
                "ORDER BY\n" +
                "\tt_sys_company.SORT_NO,\n" +
                "\tt_sys_role_name.SORT_NO,\n" +
                "\tt_sys_depart.SORT_NO\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getNewSaleManagerList(String userId, String dateTime) {
        StringBuffer sb = new StringBuffer();
        sb.append("  SELECT\n" +
                "\tt_sys_user.`NAME` as userName,\n" +
                "t_sys_user.ID as userId,\n" +
                "t_sys_company.SORT_NO as companySortNo,\n" +
                "t_sys_role_name.SORT_NO as roleNameSortNo,\n" +
                "t_sys_region.SORT_NO as regionSortNo,\n" +
                "CONCAT(t_sys_region_achivements.BEGIN_MONTH,'-',t_sys_region_achivements.END_MONTH) AS cycle,\n" +
                " IFNULL(t_sys_bonus.SALE_TARGET_NUM,0) as targetNum,\n" +
                " IFNULL(t_sys_bonus.SALE_FINISH_NUM,0) as finishNum,\n" +
                "  IFNULL(\n" +
                "\t\t-t_sys_bonus.SALE_PUNISHMENT,\n" +
                "\t\t0\n" +
                "\t) AS salePunishment,\n" +
                "\tIFNULL(t_sys_bonus.SALE_REWARD, 0) AS saleReward,\n" +
                "(\n" +
                "\t\tIFNULL(\n" +
                "\t\t\tt_sys_bonus.SALE_REWARD,\n" +
                "\t\t\t0\n" +
                "\t\t) + IFNULL(-t_sys_bonus.SALE_PUNISHMENT, 0)\n" +
                "\t) AS money\n" +
                "FROM\n" +
                "\t`t_sys_bonus` \n" +
                "LEFT JOIN t_sys_user ON t_sys_bonus.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_region_achivements ON t_sys_region_achivements.USER_ID=t_sys_bonus.USER_ID\n" +
                "AND t_sys_region_achivements.AREA_ID=t_sys_bonus.BIG_REGION_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.ID=t_sys_depart.COMPANY_ID\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID=t_sys_region_achivements.AREA_ID\n" +
                "WHERE 1=1\n" +
                "AND t_sys_region.DEL_FLAG=0\n" +
                "AND t_sys_role.DEL_FLAG=0\n" +
                "AND t_sys_role_name.DEL_FLAG=0\n" +
                "AND t_sys_company.DEL_FLAG=0\n" +
                "AND t_sys_depart.DEL_FLAG=0\n" +
                "AND t_sys_bonus.USER_ID in (" + userId + ")\n" +
                "AND t_sys_bonus.TYPE=1 AND left(day,7)='" + dateTime + "'\n" +
                "GROUP BY t_sys_bonus.USER_ID\n" +
                "ORDER BY t_sys_company.SORT_NO,t_sys_depart.SORT_NO,t_sys_role_name.SORT_NO,t_sys_user.`NAME`\n");
        List<JSONObject> list = super.queryForJsonList(sb.toString());
        return list;
    }

    public List<JSONObject> getNewSaleBigManagerList(String userId, String dateTime, String month) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT\n" +
                "  t_sys_bonus.COMPANY_ID as companyId,\n" +
                "  t_sys_region.`NAME` as regionName,\n" +
                "t_sys_role_name.SORT_NO as roleNameSortNo,\n" +
                "t_sys_company.SORT_NO as companySortNo,\n" +
                "\tt_sys_user.`NAME` as userName,\n" +
                "t_sys_user.ID as userId,\n" +
                "'" + month + "' as cycle,\n" +
                "t_sys_region.SORT_NO as regionSortNo,\n" +
                "  t_sys_bonus.ROLE_NAME as roleName,\n" +
                " IFNULL(t_sys_big_achievements.SALE_TARGET_NUM,0) as targetNum,\n" +
                "IFNULL(t_sys_big_achievements.SALE_FINISH_NUM,0) as finishNum," +
                "\n" +
                "  IFNULL(\n" +
                "\t\t-t_sys_bonus.SALE_PUNISHMENT,\n" +
                "\t\t0\n" +
                "\t) AS salePunishment,\n" +
                "\tIFNULL(t_sys_bonus.SALE_REWARD, 0) AS saleReward,\n" +
                "(\n" +
                "\t\tIFNULL(\n" +
                "\t\t\tt_sys_bonus.SALE_REWARD,\n" +
                "\t\t\t0\n" +
                "\t\t) + IFNULL(-t_sys_bonus.SALE_PUNISHMENT, 0)\n" +
                "\t) AS money\n" +
                "FROM\n" +
                "\t`t_sys_bonus` \n" +
                "LEFT JOIN t_sys_user ON t_sys_bonus.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.id=t_sys_bonus.BIG_REGION_ID \n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID=t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID=t_sys_depart.ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID=t_sys_company.ID\n" +
                "LEFT JOIN t_sys_big_achievements ON t_sys_big_achievements.BIG_AREA_ID=t_sys_bonus.BIG_REGION_ID\n" +
                "AND t_sys_big_achievements.`MONTH`='" + dateTime + "'\n" +
                "WHERE t_sys_user.id='" + userId + "'\n" +
                "AND t_sys_bonus.TYPE=1 AND left(day,7)='" + dateTime + "'\n" +
                "AND t_sys_bonus.ROLE_NAME=\"大区经理\"" +
                "AND t_sys_role.DEL_FLAG=0\n" +
                "AND t_sys_role_name.DEL_FLAG=0\n" +
                "AND t_sys_company.DEL_FLAG=0\n" +
                "AND t_sys_depart.DEL_FLAG=0\n" +
                "ORDER BY t_sys_company.SORT_NO,t_sys_depart.SORT_NO,t_sys_role_name.SORT_NO");
        List<JSONObject> list = super.queryForJsonList(sb.toString());
        return list;
    }

    public List<JSONObject> getTodayWechatShareList(String dateTime, String yesterday, String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tfinishForward.userId,\n" +
                "\tIFNULL((contacts.num), 0) AS num,\n" +
                "\tIFNULL((forward.forward), 0) AS forward,\n" +
                "\tIFNULL(\n" +
                "\t\t(\n" +
                "\t\t\tfinishForward.finishForward\n" +
                "\t\t),\n" +
                "\t\t0\n" +
                "\t) AS finishForward,\n" +
                "\tCASE\n" +
                "WHEN (\n" +
                "\tIFNULL((forward.forward), 0) - IFNULL(\n" +
                "\t\t(\n" +
                "\t\t\tfinishForward.finishForward\n" +
                "\t\t),\n" +
                "\t\t0\n" +
                "\t)\n" +
                ") > 0 THEN\n" +
                "\t(\n" +
                "\t\tIFNULL((forward.forward), 0) - IFNULL(\n" +
                "\t\t\t(\n" +
                "\t\t\t\tfinishForward.finishForward\n" +
                "\t\t\t),\n" +
                "\t\t\t0\n" +
                "\t\t)\n" +
                "\t)\n" +
                "ELSE\n" +
                "\t0\n" +
                "END noForward\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tCOUNT(1) AS finishForward,\n" +
                "\t\t\t'" + userId + "' AS userId,\n" +
                "\t\t\t'" + dateTime + "' AS shareDate\n" +
                "\t\tFROM\n" +
                "\t\t\tv_sys_wechat_finish\n" +
                "\t\tWHERE\n" +
                "\t\t\tv_sys_wechat_finish.userId = '" + userId + "'\n" +
                "\t\tAND LEFT (\n" +
                "\t\t\tv_sys_wechat_finish.createTime,\n" +
                "\t\t\t10\n" +
                "\t\t) = '" + dateTime + "'\n" +
                "\t) AS finishForward\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tCOUNT(1) AS forward,\n" +
                "\t\tv_sys_wechat_forward.shareDate AS shareDate\n" +
                "\tFROM\n" +
                "\t\tv_sys_wechat_forward\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND shareDate = '" + dateTime + "'\n" +
                "\tAND companyId IN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tv_sys_user.companyId\n" +
                "\t\tFROM\n" +
                "\t\t\tv_sys_user\n" +
                "\t\tWHERE\n" +
                "\t\t\tid = '" + userId + "'\n" +
                "\t)\n" +
                ") AS forward ON finishForward.shareDate = forward.shareDate\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\t'" + userId + "' AS userId,\n" +
                "\t\t(\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tIFNULL(\n" +
                "\t\t\t\t\t\tt_wechat_friends_contacts.CONTACTS,\n" +
                "\t\t\t\t\t\t0\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tt_wechat_friends_contacts\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\tUSER_ID = '" + userId + "'\n" +
                "\t\t\t\tAND LEFT (CREATE_TIME, 10) = '" + dateTime + "'\n" +
                "\t\t\t\tORDER BY\n" +
                "\t\t\t\t\tCREATE_TIME DESC\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t) - (\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tIFNULL(\n" +
                "\t\t\t\t\t\tt_wechat_friends_contacts.CONTACTS,\n" +
                "\t\t\t\t\t\t0\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tt_wechat_friends_contacts\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\tUSER_ID = '" + userId + "'\n" +
                "\t\t\t\tAND LEFT (CREATE_TIME, 10) < '" + yesterday + "'\n" +
                "\t\t\t\tORDER BY\n" +
                "\t\t\t\t\tCREATE_TIME DESC\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\t) AS num\n" +
                "\tFROM\n" +
                "\t\tt_wechat_friends_contacts\n" +
                "\tWHERE\n" +
                "\t\tt_wechat_friends_contacts.USER_ID = '" + userId + "'\n" +
                "\tAND LEFT (\n" +
                "\t\tt_wechat_friends_contacts.CREATE_TIME,\n" +
                "\t\t10\n" +
                "\t) = '" + dateTime + "'\n" +
                ") AS contacts ON contacts.userId = finishForward.userId");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getRegionManagerByUserId(String userId, String dateTime, String regionId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT v_sys_user.id as userId\n" +
                "FROM v_sys_user\n" +
                "LEFT JOIN t_sys_user_leave ON t_sys_user_leave.USER_ID = v_sys_user.id\n" +
                "LEFT JOIN t_sys_user_2_region on t_sys_user_2_region.USER_ID=v_sys_user.id\n" +
                "LEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID=t_sys_region.ID\n" +
                "WHERE 1=1\n" +
                "and t_sys_region.DEL_FLAG=0\n" +
                " AND t_sys_region.PARENT_ID=0\n");
        if (!StringUtils.isBlank(regionId)) {
            sql.append("AND t_sys_region.ID='" + regionId + "'\n");
        }
        sql.append("\tAND v_sys_user.id in (" + userId + ")\n" +
                "AND roleName in ('区域经理','区域组长','大区经理')\n" +
                "AND (v_sys_user.jobState!=1 OR t_sys_user_leave.LEAVE_DATE='" + dateTime + "')\n\n" +
                "GROUP BY v_sys_user.id\n" +
                "ORDER BY roleSortNo\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getIntentCustomerListByUserId(String userIds, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "userId.userId,\n" +
                "\tifnull(withinOneMonth.withinOneMonth,0) as withinOneMonth,\n" +
                "\tifnull(moreThanOneMonth.moreThanOneMonth,0) as moreThanOneMonth\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tid AS userId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user\n" +
                "\t\tWHERE\n" +
                "\t\t\tID IN (" + userIds + ")\n" +
                "\t) AS userId\n" +
                "\tLEFT JOIN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tCOUNT(1) AS withinOneMonth,\n" +
                "\t\t\tt_wrt_customer_visit.CREATE_USER_ID AS userId\n" +
                "\t\tFROM\n" +
                "\t\t\t(SELECT\n" +
                "\tt.*\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\t*\n" +
                "\t\tFROM\n" +
                "\t\t\t`t_wrt_customer_visit`\n" +
                "\t\tWHERE\n" +
                "\t\t\tCREATE_USER_ID IN (" + userIds + ")\n" +
                "AND CUSTOMER_ID IS not NULL\n" +
                "\t\tAND LEFT (\n" +
                "\t\t\tt_wrt_customer_visit.REPORT_DATE,\n" +
                "\t\t\t7\n" +
                "\t\t) = '" + dateTime + "'\n" +
                "\t\tORDER BY\n" +
                "\t\t\t`create_time` DESC\n" +
                "\t\tLIMIT 10000000000\n" +
                "\t) t\n" +
                "GROUP BY\n" +
                "\tt.CREATE_USER_ID,t.customer_id) as t_wrt_customer_visit\n\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_wrt_customer_visit.CREATE_USER_ID in (" + userIds + ")\n" +
                "\t\tAND t_wrt_customer_visit.BUY_DATE = '一月内'\n" +
                "\t\tAND LEFT (\n" +
                "\t\t\tt_wrt_customer_visit.REPORT_DATE,\n" +
                "\t\t\t7\n" +
                "\t\t) = '" + dateTime + "'\n" +
                "\t\tGROUP BY\n" +
                "\t\t\tt_wrt_customer_visit.CREATE_USER_ID\n" +
                "\t) AS withinOneMonth ON withinOneMonth.userId=userId.userId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\t COUNT(1) AS moreThanOneMonth,\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID AS userId\n" +
                "\tFROM\n" +
                "\t\t(SELECT\n" +
                "\tt.*\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\t*\n" +
                "\t\tFROM\n" +
                "\t\t\t`t_wrt_customer_visit`\n" +
                "\t\tWHERE\n" +
                "\t\t\tCREATE_USER_ID IN (" + userIds + ")\n" +
                "AND CUSTOMER_ID IS not NULL\n" +
                "\t\tAND LEFT (\n" +
                "\t\t\tt_wrt_customer_visit.REPORT_DATE,\n" +
                "\t\t\t7\n" +
                "\t\t) = '" + dateTime + "'\n" +
                "\t\tORDER BY\n" +
                "\t\t\t`create_time` DESC\n" +
                "\t\tLIMIT 10000000000\n" +
                "\t) t\n" +
                "GROUP BY\n" +
                "\tt.CREATE_USER_ID,t.customer_id) as t_wrt_customer_visit\n\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_wrt_customer_visit.CREATE_USER_ID in ( " + userIds + ")\n" +
                "\tAND t_wrt_customer_visit.BUY_DATE = '一月以上'\n" +
                "\tAND LEFT (\n" +
                "\t\tt_wrt_customer_visit.REPORT_DATE,\n" +
                "\t\t7\n" +
                "\t) = '" + dateTime + "'\n" +
                "\tGROUP BY\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID\n" +
                ") AS moreThanOneMonth ON withinOneMonth.userId = moreThanOneMonth.userId\n");
        return super.queryForJsonList(sql.toString());
    }

    public JSONObject getBonusTodayMoneyByUserId(String userId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tIFNULL(SUM(t_sys_bonus.MONEY),0) AS todayMoney\n" +
                "FROM\n" +
                "\tt_sys_bonus\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_bonus.TYPE = 0\n" +
                "AND t_sys_bonus.`DAY` = '" + userId + "'\n" +
                "AND t_sys_bonus.USER_ID = '" + dateTime + "'\n" +
                "GROUP BY\n" +
                "\tt_sys_bonus.USER_ID\n");
        return super.queryForJsonObject(sql.toString());
    }


    public List<JSONObject> getVisitPeopleByUserIdAndDateTime(String userId, String dateTime, String type, StringBuffer condition, int requestPage, int pageSize) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_wrt_customer.`NAME` AS `customerName`,\n" +
                "\tt_wrt_customer.ID AS customerId,\n" +
                "\tt_wrt_customer.TEL AS customerTel,\n" +
                "\tt_wrt_customer.WECHAT AS customerWechat,\n" +
                "\tt_wrt_customer_visit.ENTOURAGE AS entourageName,\n" +
                "\tt_wrt_customer_visit.ENTOURAGE_ID AS entourageId,\n" +
                "\tt_wrt_customer_visit.CUSTOMER_TYPE AS customerType,\n" +
                "\tt_wrt_customer_visit.CUSTOMER_CREDIT AS customerCredit,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_TIME AS arrivalsTime,\n" +
                "\tt_wrt_customer_visit.LEAVE_TIME AS leaveTime,\n" +
                "\tt_wrt_customer_visit.LEAVE_IMG AS leaveImg,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_IMG AS arrivalsImg,\n" +
                "\tt_wrt_customer_visit.OTHER_CONDITION AS otherCondition,\n" +
                "\tIFNULL(\n" +
                "\t\tCEIL(\n" +
                "\t\t\tCEILING(\n" +
                "\t\t\t\tUNIX_TIMESTAMP(\n" +
                "\t\t\t\t\tt_wrt_customer_visit.LEAVE_TIME\n" +
                "\t\t\t\t) - UNIX_TIMESTAMP(\n" +
                "\t\t\t\t\tt_wrt_customer_visit.ARRIVALS_TIME\n" +
                "\t\t\t\t)\n" +
                "\t\t\t) / 60\n" +
                "\t\t),\n" +
                "\t\t0\n" +
                "\t) AS time\n" +
                "FROM\n" +
                "\tt_wrt_customer_visit\n" +
                "LEFT JOIN t_wrt_customer ON t_wrt_customer_visit.CUSTOMER_ID = t_wrt_customer.ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_wrt_customer_visit.CREATE_USER_ID = '" + userId + "'\n" +
                "AND t_wrt_customer_visit.CUSTOMER_ID IS NOT NULL\n");
        if (dateTime.length() == 10) {
            sql.append("AND REPORT_DATE='" + dateTime + "'\n");

        } else {
            sql.append("AND left(REPORT_DATE,7)='" + dateTime + "'\n");

        }
        sql.append(condition);
        sql.append("AND VISIT_MODE=" + type + "\n" +
                "ORDER BY\n" +
                "\tt_wrt_customer_visit.ARRIVALS_TIME DESC\n");
        super.appendPageSql(sql, requestPage, pageSize);

        return super.queryForJsonList(sql.toString());
    }

    public Integer getVisitPeoplePageCount(String userId, String dateTime, String type, StringBuffer condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT count(1) as count from(SELECT\n" +
                "\tt_wrt_customer.`NAME` AS `customerName`,\n" +
                "\tt_wrt_customer.ID AS customerId,\n" +
                "\tt_wrt_customer.TEL AS customerTel,\n" +
                "\tt_wrt_customer.WECHAT AS customerWechat,\n" +
                "\tt_wrt_customer_visit.ENTOURAGE AS entourageName,\n" +
                "\tt_wrt_customer_visit.ENTOURAGE_ID AS entourageId,\n" +
                "\tt_wrt_customer_visit.CUSTOMER_TYPE AS customerType,\n" +
                "\tt_wrt_customer_visit.CUSTOMER_CREDIT AS customerCredit,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_TIME AS arrivalsTime,\n" +
                "\tt_wrt_customer_visit.LEAVE_TIME AS leaveTime,\n" +
                "\tt_wrt_customer_visit.LEAVE_IMG AS leaveImg,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_IMG AS arrivalsImg,\n" +
                "\tt_wrt_customer_visit.OTHER_CONDITION AS otherCondition,\n" +
                "\tIFNULL(\n" +
                "\t\tCEIL(\n" +
                "\t\t\tCEILING(\n" +
                "\t\t\t\tUNIX_TIMESTAMP(\n" +
                "\t\t\t\t\tt_wrt_customer_visit.LEAVE_TIME\n" +
                "\t\t\t\t) - UNIX_TIMESTAMP(\n" +
                "\t\t\t\t\tt_wrt_customer_visit.ARRIVALS_TIME\n" +
                "\t\t\t\t)\n" +
                "\t\t\t) / 60\n" +
                "\t\t),\n" +
                "\t\t0\n" +
                "\t) AS time\n" +
                "FROM\n" +
                "\tt_wrt_customer_visit\n" +
                "LEFT JOIN t_wrt_customer ON t_wrt_customer_visit.CUSTOMER_ID = t_wrt_customer.ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_wrt_customer_visit.CREATE_USER_ID = '" + userId + "'\n" +
                "AND t_wrt_customer_visit.CUSTOMER_ID IS NOT NULL\n");
        if (dateTime.length() == 10) {
            sql.append("AND REPORT_DATE='" + dateTime + "'\n");

        } else {
            sql.append("AND left(REPORT_DATE,7)='" + dateTime + "'\n");

        }
        sql.append(condition);
        sql.append("AND VISIT_MODE=" + type + "\n" +
                "ORDER BY\n" +
                "\tt_wrt_customer_visit.ARRIVALS_TIME DESC) as num\n");
        String count = super.queryForString(sql.toString(), new Object[]{});
        return Integer.parseInt(count);
    }

    public List<JSONObject> getForGroupVacantAndFine(String month, String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tsyst.companyId,\n" +
                "\tsyst.companyName,\n" +
                "\tsyst.regionId,\n" +
                "\tsyst.regionName,\n" +
                "\tsyst.userId,\n" +
                "\tsyst.userName,\n" +
                "\tsyst.roleName,\n" +
                "\tsyst.currentRole,\n" +
                "\tsyst.roleSortNo,\n" +
                "\tsyst.userSortNo,\n" +
                "\tsyst.money,\n" +
                "\tsyst.companySortNo,\n" +
                "\tsyst.regionSortNo\n" +
                "FROM(\n" +
                "\tSELECT\n" +
                "\tsysBig.companyId,\n" +
                "\tsysBig.companyName,\n" +
                "\tsysBig.regionId,\n" +
                "\tsysBig.regionName,\n" +
                "\tsysBig.userId,\n" +
                "\tsysBig.userName,\n" +
                "\tsysBig.roleName,\n" +
                "\tCASE\n" +
                "WHEN '大区经理' IN (\n" +
                "\tSELECT\n" +
                "\t\troleName\n" +
                "\tFROM\n" +
                "\t\tv_sys_user\n" +
                "\tWHERE\n" +
                "\t\tid = sysBig.userId\n" +
                ") THEN\n" +
                "\t'true'\n" +
                "ELSE\n" +
                "\t'false'\n" +
                "END AS currentRole,\n" +
                " sysBig.roleSortNo,\n" +
                " sysBig.userSortNo,\n" +
                " sysBig.money,\n" +
                " sysBig.companySortNo,\n" +
                " sysBig.regionSortNo\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_company.ID AS companyId,\n" +
                "\t\t\tt_sys_company.`NAME` AS companyName,\n" +
                "\t\t\tt_sys_region.ID AS regionId,\n" +
                "\t\t\tt_sys_region.`NAME` AS regionName,\n" +
                "\t\t\tv_sys_user.id AS userId,\n" +
                "\t\t\tv_sys_user.`name` AS userName,\n" +
                "\t\t\tCASE\n" +
                "\t\tWHEN v_sys_user.roleName = '大区经理' THEN\n" +
                "\t\t\tv_sys_user.roleName\n" +
                "\t\tELSE\n" +
                "\t\t\tt_sys_bonus.ROLE_NAME\n" +
                "\t\tEND AS roleName,\n" +
                "\t\tv_sys_user.roleSortNo AS roleSortNo,\n" +
                "\t\tv_sys_user.userSortNo AS userSortNo,\n" +
                "\t\tIFNULL(t_sys_bonus.MONEY, 0) AS money,\n" +
                "\t\tt_sys_company.SORT_NO AS companySortNo,\n" +
                "\t\tt_sys_region.SORT_NO AS regionSortNo\n" +
                "\tFROM\n" +
                "\t\tt_sys_company\n" +
                "\tLEFT JOIN t_sys_region ON t_sys_company.ID = t_sys_region.COMPANY_ID\n" +
                "\tAND t_sys_region.DEL_FLAG = 0\n" +
                "\tAND t_sys_region.PARENT_ID = '0'\n" +
                "\tLEFT JOIN t_sys_user_2_region ON t_sys_region.ID = t_sys_user_2_region.REGION_ID\n" +
                "\tLEFT JOIN v_sys_user ON t_sys_user_2_region.USER_ID = v_sys_user.id\n" +
                "\tAND t_sys_region.`NAME` = v_sys_user.departName\n" +
                "\tAND v_sys_user.jobState <> 1\n" +
                "\tLEFT JOIN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tCOMPANY_ID,\n" +
                "\t\t\tBIG_REGION_ID,\n" +
                "\t\t\tUSER_ID,\n" +
                "\t\t\tROLE_NAME,\n" +
                "\t\t\tSUM(MONEY) AS money\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_bonus\n" +
                "\t\tWHERE\n" +
                "\t\t\tleft(`DAY`,7) = '" + month + "'\n" +
                "\t\tAND TYPE = 0\n" +
                "\t\tAND ROLE_NAME = '大区经理'\n" +
                "\t\tGROUP BY\n" +
                "\t\t\tCOMPANY_ID,\n" +
                "\t\t\tBIG_REGION_ID,\n" +
                "\t\t\tUSER_ID,\n" +
                "\t\t\tROLE_NAME\n" +
                "\t) AS t_sys_bonus ON t_sys_company.ID = t_sys_bonus.COMPANY_ID\n" +
                "\tAND t_sys_region.ID = t_sys_bonus.BIG_REGION_ID\n" +
                "\tAND v_sys_user.id = t_sys_bonus.USER_ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_sys_company.PARENT_ID = '201712161613432EAF27DBCCB7D0E6CA'\n" +
                "\tAND t_sys_company.ID NOT IN (\n" +
                "\t\t'2017121617484A2796D8F52B6B558B94'\n" +
                "\t)\n" +
                "\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\tAND (\n" +
                "\t\tv_sys_user.roleName = '大区经理'\n" +
                "\t\tOR (\n" +
                "\t\t\t'大区经理' NOT IN (\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tt.roleName\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tv_sys_user AS t\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\tt.id = v_sys_user.id\n" +
                "\t\t\t)\n" +
                "\t\t\tAND t_sys_bonus.ROLE_NAME = '大区经理'\n" +
                "\t\t)\n" +
                "\t)\n" +
                "\tAND v_sys_user.id IS NOT NULL\n" +
                "\tAND v_sys_user.companyId = t_sys_company.ID\n" +
                "\tGROUP BY\n" +
                "\t\tt_sys_company.ID,\n" +
                "\t\tt_sys_region.ID,\n" +
                "\t\tv_sys_user.id,\n" +
                "\t\troleName\n" +
                "\t) AS sysBig\n" +
                "\t) AS syst\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND userId = '" + userId + "'\n" +
                "GROUP BY\n" +
                "\tcompanyId,\n" +
                "\tregionId,\n" +
                "\tuserId,\n" +
                "\troleName\n" +
                "ORDER BY\n" +
                "\tcompanySortNo,\n" +
                "\tregionSortNo,\n" +
                "\troleSortNo DESC,\n" +
                "\tuserSortNo DESC\n");
        List<JSONObject> list = super.queryForJsonList(sql.toString());
        return list;
    }

    public List<JSONObject> getForDepartBycompanyId(String dateTime, String companyId, String userIds) {
        StringBuffer sql = new StringBuffer();

        sql.append(" SELECT");
        sql.append(" sysUser.userId as userId,");
        sql.append(" sysUser.departName AS departName,");
        sql.append(" sysUser.roleName AS roleName,");
        sql.append(" sysUser.userName AS userName,");
        sql.append(" sysUser.regionName AS regionName,");
        sql.append(" sysUser.reportDate AS reportDate,");
        sql.append(" IFNULL((wechatforward.forward), 0) AS forward,");
        sql.append(" IFNULL((wechatfinish.FinishForward), 0) AS finishForward,");
        sql.append(" CASE WHEN (IFNULL((wechatforward.forward), 0)-IFNULL((wechatfinish.FinishForward), 0))>0");
        sql.append(" THEN (IFNULL((wechatforward.forward), 0)-IFNULL((wechatfinish.FinishForward), 0))");
        sql.append(" ELSE 0 END noForward");
        sql.append(" FROM(");
        sql.append(" SELECT companyId,userId,departName,roleName,userName,regionName,'" + dateTime + "' as reportDate");
        sql.append(" FROM v_sys_user_for_report");
        sql.append(" WHERE 1 = 1");


        sql.append(" AND  userId in(" + userIds + ")");

        sql.append(" GROUP BY userId,departName,roleName,userName,regionName ");
        sql.append(" ORDER BY companySortNo,roleSortNo,departSortNo,userName) as sysUser");
        sql.append(" LEFT JOIN (");
        sql.append(" SELECT count(1) as FinishForward,");
        sql.append(" v_sys_wechat_finish.userId AS userId");
        sql.append(" FROM v_sys_wechat_finish");
        sql.append(" WHERE 1 = 1 AND ");

        sql.append(" LEFT(v_sys_wechat_finish.createTime,7)= '" + dateTime + "'");

        sql.append(" group by v_sys_wechat_finish.userId");
        sql.append(" ) as wechatfinish on wechatfinish.userId=sysUser.userId");
        sql.append(" LEFT JOIN( ");
        sql.append(" SELECT ");

        sql.append(" (sum(v_sys_wechat_forward.forwardNumber)-0) as forward,v_sys_wechat_forward.companyId as companyId,v_sys_wechat_forward.shareDate as shareDate");
        sql.append("   from v_sys_wechat_forward");
        sql.append("  WHERE 1 = 1 AND left(shareDate,7)= '" + dateTime + "' AND companyId = '" + companyId + "')");

        sql.append(" as wechatforward on wechatforward.companyId=sysUser.companyId");

        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getContactsByUserId(String userIds, String yesterday, String beforeYesterday) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tsysUser.userId,\n" +
                "\tifnull(todayContacts.contacts-yestodayContacts.contacts, 0) as num,\n" +
                "ifnull(\n" +
                "\t\ttodayContacts.contacts ,\n" +
                "\t\t0\n" +
                "\t) AS contacts\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_user.id AS userId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user\n" +
                "\t\tWHERE\n" +
                "\t\t\tid IN (" + userIds + ")\n" +
                "\t) AS sysUser\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_wechat_friends_contacts.USER_ID AS userId,\n" +
                "\t\tt_wechat_friends_contacts.CONTACTS AS contacts\n" +
                "\tFROM\n" +
                "\t\tt_wechat_friends_contacts\n" +
                "\tWHERE\n" +
                "\t\tt_wechat_friends_contacts.USER_ID IN (" + userIds + ")\n" +
                "\tAND LEFT (\n" +
                "\t\tt_wechat_friends_contacts.CREATE_TIME,\n" +
                "\t\t10\n" +
                "\t) = '" + yesterday + "'\n" +
                ") AS todayContacts ON todayContacts.userId = sysUser.userId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_wechat_friends_contacts.USER_ID AS userId,\n" +
                "\t\tt_wechat_friends_contacts.CONTACTS AS contacts\n" +
                "\tFROM\n" +
                "\t\tt_wechat_friends_contacts\n" +
                "\tWHERE\n" +
                "\t\tt_wechat_friends_contacts.USER_ID IN (" + userIds + ")\n" +
                "\tAND LEFT (\n" +
                "\t\tt_wechat_friends_contacts.CREATE_TIME,\n" +
                "\t\t10\n" +
                "\t) = '" + beforeYesterday + "'\n" +
                ") AS yestodayContacts ON yestodayContacts.userId = sysUser.userId");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getFinishByUserIdAndDateTime(String userId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_con_main.CONTRACT_NO as contractNo, \n" +
                "\tt_con_main.MODEL_NAME as modelName,\n" +
                "\tt_con_main.DELIVERY_DATE as deliveryDate,\n" +
                "\tt_con_main.BRAND_NAME as brandName,\n" +
                "\tt_con_main.SERIAL_NO as serialNno,\n" +
                "\tt_con_main.REGION_MANAGER_NAME as regionManagerName\n" +
                "FROM\n" +
                "\tt_con_main\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_con_main.REGION_MANAGER_ID in (" + userId + ")\n" +
                "AND LEFT (t_con_main.CREATE_TIME, 7) in (" + dateTime + ")\n" +
                "AND t_con_main.AUDIT_FLAG = 1\n" +
                "AND t_con_main.HAVE_DELIVERY = 1");
        return super.queryForJsonList(sql.toString());
    }

    public JSONObject getRegionAchivementsByUserIdAndDateTime(String userId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_sys_region_achivements.BEGIN_MONTH AS beginMonth,\n" +
                "\tt_sys_region_achivements.MIDDLE_MONTH AS middleMonth,\n" +
                "\tt_sys_region_achivements.END_MONTH AS endMonth\n" +
                "FROM\n" +
                "\tt_sys_region_achivements\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND (\n" +
                "\tt_sys_region_achivements.BEGIN_MONTH = '" + dateTime + "'\n" +
                "\tOR t_sys_region_achivements.MIDDLE_MONTH = '" + dateTime + "'\n" +
                "\tOR t_sys_region_achivements.END_MONTH = '" + dateTime + "'\n" +
                ")\n" +
                "AND USER_ID in ( " + userId + ")\n");
        return super.queryForJsonObject(sql.toString());
    }

    public List<JSONObject> getWechatShareList(String userId, String dateTime, String companyId, String today, Integer type) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tweChat.*\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tT_SYS_WECHAT_SHARE.ID AS id,\n" +
                "\t\t\tT_SYS_WECHAT_SHARE.COMPANY_ID AS companyId,\n" +
                "\t\t\tT_SYS_WECHAT_SHARE.SHARE_DATE shareDate,\n" +
                "\t\t\tT_SYS_WECHAT_SHARE. NAME AS name,\n" +
                "\t\t\tT_SYS_WECHAT_SHARE.LINK_URL AS linkUrl,\n" +
                "\t\t\tT_SYS_WECHAT_SHARE.IMAGE_URL AS imageUrl,\n" +
                "\t\t\tT_SYS_WECHAT_SHARE.FORWARD_NUMBER AS forwardNumber,\n" +
                "\t\t\tT_SYS_WECHAT_SHARE.CREATE_USER_ID AS createUserId,\n" +
                "\t\t\tT_SYS_WECHAT_SHARE.CREATE_TIME AS createTime,\n" +
                "\t\t\tT_SYS_WECHAT_SHARE.REMARK AS remark\n" +
                "\t\tFROM\n" +
                "\t\t\tT_SYS_WECHAT_SHARE\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND LEFT (\n" +
                "\t\t\tT_SYS_WECHAT_SHARE.SHARE_DATE,\n" +
                "\t\t\t7\n" +
                "\t\t) = '" + dateTime + "'\n" +
                "\t\tAND T_SYS_WECHAT_SHARE.COMPANY_ID = '" + companyId + "'\n" +
                "\t) AS weChat\n" +
                "LEFT JOIN t_sys_wechat_share_detail ON t_sys_wechat_share_detail.WECHAT_SHARE_ID = weChat.ID AND t_sys_wechat_share_detail.DEL_FLAG=0\n" +
                "AND LEFT (\n" +
                "\tt_sys_wechat_share_detail.CREATE_TIME,\n" +
                "\t7\n" +
                ") = '" + dateTime + "'\n" +
                "AND t_sys_wechat_share_detail.USER_ID = '" + userId + "'\n" +
                "WHERE 1=1\n" +
                "AND weChat.shareDate<='" + today + "'\n");
        //应转发	已转发	未转发   0-1-2
        switch (type) {
            case 0:
                sql.append("AND 1=1\n");
                break;
            case 1:
                sql.append("AND t_sys_wechat_share_detail.ID is not null\n");
                break;
            case 2:
                sql.append("AND t_sys_wechat_share_detail.ID is null\n");
                break;
            default:
                sql.append("AND 1=1\n");
        }
        sql.append("ORDER BY weChat.shareDate desc\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getAllCustomerForRM(String userId, StringBuffer condition, int requestPage, int pageSize) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_wrt_customer.`NAME` AS `customerName`,\n" +
                "\tt_wrt_customer.ID AS customerId,\n" +
                "\tt_wrt_customer.TEL AS customerTel,\n" +
                "\tt_wrt_customer.WECHAT AS customerWechat,\n" +
                "\tt_wrt_customer_visit.ENTOURAGE AS entourageName,\n" +
                "\tt_wrt_customer_visit.ENTOURAGE_ID AS entourageId,\n" +
                "\tt_wrt_customer_visit.CUSTOMER_TYPE AS customerType,\n" +
                "\tt_wrt_customer_visit.CUSTOMER_CREDIT AS customerCredit,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_TIME AS arrivalsTime,\n" +
                "\tt_wrt_customer_visit.LEAVE_TIME AS leaveTime,\n" +
                "\tt_wrt_customer_visit.LEAVE_IMG AS leaveImg,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_IMG AS arrivalsImg,\n" +
                "\tt_wrt_customer_visit.OTHER_CONDITION AS otherCondition,\n" +
                "\tIFNULL(\n" +
                "\t\tCEIL(\n" +
                "\t\t\tCEILING(\n" +
                "\t\t\t\tUNIX_TIMESTAMP(\n" +
                "\t\t\t\t\tt_wrt_customer_visit.LEAVE_TIME\n" +
                "\t\t\t\t) - UNIX_TIMESTAMP(\n" +
                "\t\t\t\t\tt_wrt_customer_visit.ARRIVALS_TIME\n" +
                "\t\t\t\t)\n" +
                "\t\t\t) / 60\n" +
                "\t\t),\n" +
                "\t\t0\n" +
                "\t) AS time\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\t*\n" +
                "\t\tFROM\n" +
                "\t\t\t`t_wrt_customer_visit`\n" +
                "\t\tWHERE\n" +
                "\t\t\tCREATE_USER_ID = '" + userId + "'\n" +
                "AND CUSTOMER_ID IS not NULL\n" +
                "\t\tORDER BY\n" +
                "\t\t\t`create_time` DESC\n" +
                "\t\t LIMIT 10000000000\n" +
                "\t) as t_wrt_customer_visit\n" +
                "LEFT JOIN t_wrt_customer ON t_wrt_customer.ID=t_wrt_customer_visit.CUSTOMER_ID\n" +
                "GROUP BY\n" +
                "\tt_wrt_customer_visit.customer_id\n");
        sql.append(condition);
        sql.append("ORDER BY t_wrt_customer_visit.ARRIVALS_TIME desc\n");
        super.appendPageSql(sql, requestPage, pageSize);

        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getNewCustomerForRM(String userId, String dateTime, StringBuffer condition, int requestPage, int pageSize) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_wrt_customer.`NAME` AS `customerName`,\n" +
                "\tt_wrt_customer.ID AS customerId,\n" +
                "\tt_wrt_customer.TEL AS customerTel,\n" +
                "\tt_wrt_customer.WECHAT AS customerWechat,\n" +
                "\tt_wrt_customer_visit.ENTOURAGE AS entourageName,\n" +
                "\tt_wrt_customer_visit.ENTOURAGE_ID AS entourageId,\n" +
                "\tt_wrt_customer_visit.CUSTOMER_TYPE AS customerType,\n" +
                "\tt_wrt_customer_visit.CUSTOMER_CREDIT AS customerCredit,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_TIME AS arrivalsTime,\n" +
                "\tt_wrt_customer_visit.LEAVE_TIME AS leaveTime,\n" +
                "\tt_wrt_customer_visit.LEAVE_IMG AS leaveImg,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_IMG AS arrivalsImg,\n" +
                "\tt_wrt_customer_visit.OTHER_CONDITION AS otherCondition,\n" +
                "\tIFNULL(\n" +
                "\t\tCEIL(\n" +
                "\t\t\tCEILING(\n" +
                "\t\t\t\tUNIX_TIMESTAMP(\n" +
                "\t\t\t\t\tt_wrt_customer_visit.LEAVE_TIME\n" +
                "\t\t\t\t) - UNIX_TIMESTAMP(\n" +
                "\t\t\t\t\tt_wrt_customer_visit.ARRIVALS_TIME\n" +
                "\t\t\t\t)\n" +
                "\t\t\t) / 60\n" +
                "\t\t),\n" +
                "\t\t0\n" +
                "\t) AS time\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\t*\n" +
                "\t\tFROM\n" +
                "\t\t\t`t_wrt_customer_visit`\n" +
                "\t\tWHERE\n" +
                "\t\t\tCREATE_USER_ID = '" + userId + "'\n" +
                "AND CUSTOMER_ID IS not NULL\n" +
                "\t\tORDER BY\n" +
                "\t\t\t`create_time` DESC\n" +
                "\t\t LIMIT 10000000000\n" +
                "\t) as t_wrt_customer_visit\n" +
                "LEFT JOIN t_wrt_customer ON t_wrt_customer.ID=t_wrt_customer_visit.CUSTOMER_ID\n");
        sql.append("GROUP BY t_wrt_customer_visit.customer_id \n" +
                "HAVING t_wrt_customer_visit.customer_id in (SELECT\n" +
                "\tt3.needId\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt1.createUserId,\n" +
                "\t\t\tt1.customerId as needId,\n" +
                "\t\t\tt2.customerId\n" +
                "\t\tFROM\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tt_wrt_customer_visit.CUSTOMER_ID AS customerId,\n" +
                "\t\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID AS createUserId\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tt_wrt_customer_visit\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t1 = 1\n" +
                "\t\t\t\tAND t_wrt_customer_visit.CREATE_USER_ID IN (\n" +
                "\t\t\t\t\t'" + userId + "'\n" +
                "\t\t\t\t)\n" +
                "\t\t\t\tAND t_wrt_customer_visit.CUSTOMER_ID IS NOT NULL\n" +
                "\t\t\t\tAND LEFT (\n" +
                "\t\t\t\t\tt_wrt_customer_visit.REPORT_DATE,\n" +
                "\t\t\t\t\t7\n" +
                "\t\t\t\t) = '" + dateTime + "'\n" +
                "\t\t\t\tGROUP BY\n" +
                "\t\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID,\n" +
                "\t\t\t\t\tt_wrt_customer_visit.CUSTOMER_ID\n" +
                "\t\t\t) AS t1\n" +
                "\t\tLEFT JOIN (\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tt_wrt_customer_visit.CUSTOMER_ID AS customerId,\n" +
                "\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID AS createUserId\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_wrt_customer_visit\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\t1 = 1\n" +
                "\t\t\tAND t_wrt_customer_visit.CREATE_USER_ID IN (\n" +
                "\t\t\t\t'" + userId + "'\n" +
                "\t\t\t)\n" +
                "\t\t\tAND t_wrt_customer_visit.CUSTOMER_ID IS NOT NULL\n" +
                "\t\t\tAND LEFT (\n" +
                "\t\t\t\tt_wrt_customer_visit.REPORT_DATE,\n" +
                "\t\t\t\t7\n" +
                "\t\t\t) != '" + dateTime + "'\n" +
                "\t\t\tGROUP BY\n" +
                "\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID,\n" +
                "\t\t\t\tt_wrt_customer_visit.CUSTOMER_ID\n" +
                "\t\t) AS t2 ON t1.createUserId = t2.createUserId\n" +
                "\t\tAND t1.customerId = t2.customerId\n" +
                "\t) AS t3\n" +
                "WHERE\n" +
                "\tt3.customerId IS NULL\n" +
                "GROUP BY t3.needId\n" +
                "\t)\n");
        sql.append(condition);
        sql.append("ORDER BY\n" +
                "\tt_wrt_customer_visit.ARRIVALS_TIME DESC\n");
        super.appendPageSql(sql, requestPage, pageSize);

        return super.queryForJsonList(sql.toString());
    }

    public Integer getAllCustomerPageCount(String userId, StringBuffer condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT count(1) as count from(\n" +
                "SELECT\n" +
                "\tt_wrt_customer.`NAME` AS `customerName`,\n" +
                "\tt_wrt_customer.ID AS customerId,\n" +
                "\tt_wrt_customer.TEL AS customerTel,\n" +
                "\tt_wrt_customer.WECHAT AS customerWechat,\n" +
                "\tt_wrt_customer_visit.ENTOURAGE AS entourageName,\n" +
                "\tt_wrt_customer_visit.ENTOURAGE_ID AS entourageId,\n" +
                "\tt_wrt_customer_visit.CUSTOMER_TYPE AS customerType,\n" +
                "\tt_wrt_customer_visit.CUSTOMER_CREDIT AS customerCredit,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_TIME AS arrivalsTime,\n" +
                "\tt_wrt_customer_visit.LEAVE_TIME AS leaveTime,\n" +
                "\tt_wrt_customer_visit.LEAVE_IMG AS leaveImg,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_IMG AS arrivalsImg,\n" +
                "\tt_wrt_customer_visit.OTHER_CONDITION AS otherCondition,\n" +
                "\tIFNULL(\n" +
                "\t\tCEIL(\n" +
                "\t\t\tCEILING(\n" +
                "\t\t\t\tUNIX_TIMESTAMP(\n" +
                "\t\t\t\t\tt_wrt_customer_visit.LEAVE_TIME\n" +
                "\t\t\t\t) - UNIX_TIMESTAMP(\n" +
                "\t\t\t\t\tt_wrt_customer_visit.ARRIVALS_TIME\n" +
                "\t\t\t\t)\n" +
                "\t\t\t) / 60\n" +
                "\t\t),\n" +
                "\t\t0\n" +
                "\t) AS time\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\t*\n" +
                "\t\tFROM\n" +
                "\t\t\t`t_wrt_customer_visit`\n" +
                "\t\tWHERE\n" +
                "\t\t\tCREATE_USER_ID = '" + userId + "'\n" +
                "AND CUSTOMER_ID IS not NULL\n" +
                "\t\tORDER BY\n" +
                "\t\t\t`create_time` DESC\n" +
                "\t\t LIMIT 10000000000\n" +
                "\t) as t_wrt_customer_visit\n" +
                "LEFT JOIN t_wrt_customer ON t_wrt_customer.ID=t_wrt_customer_visit.CUSTOMER_ID\n" +
                "GROUP BY\n" +
                "\tt_wrt_customer_visit.customer_id\n");
        sql.append(condition);
        sql.append(") as num\n");
        String count = super.queryForString(sql.toString(), new Object[]{});
        return Integer.parseInt(count);
    }

    public Integer getNewCustomerPageCount(String userId, String dateTime, StringBuffer condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT count(1) as count from( SELECT\n" +
                "\tt_wrt_customer.`NAME` AS `customerName`,\n" +
                "\tt_wrt_customer.ID AS customerId,\n" +
                "\tt_wrt_customer.TEL AS customerTel,\n" +
                "\tt_wrt_customer.WECHAT AS customerWechat,\n" +
                "\tt_wrt_customer_visit.ENTOURAGE AS entourageName,\n" +
                "\tt_wrt_customer_visit.ENTOURAGE_ID AS entourageId,\n" +
                "\tt_wrt_customer_visit.CUSTOMER_TYPE AS customerType,\n" +
                "\tt_wrt_customer_visit.CUSTOMER_CREDIT AS customerCredit,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_TIME AS arrivalsTime,\n" +
                "\tt_wrt_customer_visit.LEAVE_TIME AS leaveTime,\n" +
                "\tt_wrt_customer_visit.LEAVE_IMG AS leaveImg,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_IMG AS arrivalsImg,\n" +
                "\tt_wrt_customer_visit.OTHER_CONDITION AS otherCondition,\n" +
                "\tIFNULL(\n" +
                "\t\tCEIL(\n" +
                "\t\t\tCEILING(\n" +
                "\t\t\t\tUNIX_TIMESTAMP(\n" +
                "\t\t\t\t\tt_wrt_customer_visit.LEAVE_TIME\n" +
                "\t\t\t\t) - UNIX_TIMESTAMP(\n" +
                "\t\t\t\t\tt_wrt_customer_visit.ARRIVALS_TIME\n" +
                "\t\t\t\t)\n" +
                "\t\t\t) / 60\n" +
                "\t\t),\n" +
                "\t\t0\n" +
                "\t) AS time\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\t*\n" +
                "\t\tFROM\n" +
                "\t\t\t`t_wrt_customer_visit`\n" +
                "\t\tWHERE\n" +
                "\t\t\tCREATE_USER_ID = '" + userId + "'\n" +
                "AND CUSTOMER_ID IS not NULL\n" +
                "\t\tORDER BY\n" +
                "\t\t\t`create_time` DESC\n" +
                "\t\t LIMIT 10000000000\n" +
                "\t) as t_wrt_customer_visit\n" +
                "LEFT JOIN t_wrt_customer ON t_wrt_customer.ID=t_wrt_customer_visit.CUSTOMER_ID\n");
        sql.append("GROUP BY t_wrt_customer_visit.customer_id \n" +
                "HAVING t_wrt_customer_visit.customer_id in (SELECT\n" +
                "\tt3.needId\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt1.createUserId,\n" +
                "\t\t\tt1.customerId as needId,\n" +
                "\t\t\tt2.customerId\n" +
                "\t\tFROM\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tt_wrt_customer_visit.CUSTOMER_ID AS customerId,\n" +
                "\t\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID AS createUserId\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tt_wrt_customer_visit\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t1 = 1\n" +
                "\t\t\t\tAND t_wrt_customer_visit.CREATE_USER_ID IN (\n" +
                "\t\t\t\t\t'" + userId + "'\n" +
                "\t\t\t\t)\n" +
                "\t\t\t\tAND t_wrt_customer_visit.CUSTOMER_ID IS NOT NULL\n" +
                "\t\t\t\tAND LEFT (\n" +
                "\t\t\t\t\tt_wrt_customer_visit.REPORT_DATE,\n" +
                "\t\t\t\t\t7\n" +
                "\t\t\t\t) = '" + dateTime + "'\n" +
                "\t\t\t\tGROUP BY\n" +
                "\t\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID,\n" +
                "\t\t\t\t\tt_wrt_customer_visit.CUSTOMER_ID\n" +
                "\t\t\t) AS t1\n" +
                "\t\tLEFT JOIN (\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tt_wrt_customer_visit.CUSTOMER_ID AS customerId,\n" +
                "\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID AS createUserId\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_wrt_customer_visit\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\t1 = 1\n" +
                "\t\t\tAND t_wrt_customer_visit.CREATE_USER_ID IN (\n" +
                "\t\t\t\t'" + userId + "'\n" +
                "\t\t\t)\n" +
                "\t\t\tAND t_wrt_customer_visit.CUSTOMER_ID IS NOT NULL\n" +
                "\t\t\tAND LEFT (\n" +
                "\t\t\t\tt_wrt_customer_visit.REPORT_DATE,\n" +
                "\t\t\t\t7\n" +
                "\t\t\t) != '" + dateTime + "'\n" +
                "\t\t\tGROUP BY\n" +
                "\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID,\n" +
                "\t\t\t\tt_wrt_customer_visit.CUSTOMER_ID\n" +
                "\t\t) AS t2 ON t1.createUserId = t2.createUserId\n" +
                "\t\tAND t1.customerId = t2.customerId\n" +
                "\t) AS t3\n" +
                "WHERE\n" +
                "\tt3.customerId IS NULL\n" +
                "GROUP BY t3.needId\n" +
                "\t)\n");
        sql.append(condition);
        sql.append("ORDER BY\n" +
                "\tt_wrt_customer_visit.ARRIVALS_TIME DESC ) as num\n");
        String count = super.queryForString(sql.toString(), new Object[]{});
        return Integer.parseInt(count);
    }

    public List<JSONObject> getIntentCustomerForMonth(String userId, String dateTime, Integer type, Integer requestPage, Integer pageSize) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_wrt_customer.`NAME` AS `customerName`,\n" +
                "\tt_wrt_customer.ID AS customerId,\n" +
                "\tt_wrt_customer.TEL AS customerTel,\n" +
                "\tt_wrt_customer.WECHAT AS customerWechat,\n" +
                "\tt_wrt_customer_visit.ENTOURAGE AS entourageName,\n" +
                "\tt_wrt_customer_visit.ENTOURAGE_ID AS entourageId,\n" +
                "\tt_wrt_customer_visit.CUSTOMER_TYPE AS customerType,\n" +
                "\tt_wrt_customer_visit.CUSTOMER_CREDIT AS customerCredit,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_TIME AS arrivalsTime,\n" +
                "\tt_wrt_customer_visit.LEAVE_TIME AS leaveTime,\n" +
                "\tt_wrt_customer_visit.LEAVE_IMG AS leaveImg,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_IMG AS arrivalsImg,\n" +
                "\tt_wrt_customer_visit.OTHER_CONDITION AS otherCondition,\n" +
                "\tIFNULL(\n" +
                "\t\tCEIL(\n" +
                "\t\t\tCEILING(\n" +
                "\t\t\t\tUNIX_TIMESTAMP(\n" +
                "\t\t\t\t\tt_wrt_customer_visit.LEAVE_TIME\n" +
                "\t\t\t\t) - UNIX_TIMESTAMP(\n" +
                "\t\t\t\t\tt_wrt_customer_visit.ARRIVALS_TIME\n" +
                "\t\t\t\t)\n" +
                "\t\t\t) / 60\n" +
                "\t\t),\n" +
                "\t\t0\n" +
                "\t) AS time\n" +
                "FROM\n" +
                "(SELECT\n" +
                "\tt.*\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\t*\n" +
                "\t\tFROM\n" +
                "\t\t\t`t_wrt_customer_visit`\n" +
                "\t\tWHERE\n" +
                "\t\t\tCREATE_USER_ID  ='" + userId + "'\n" +
                "AND CUSTOMER_ID IS not NULL\n" +
                "\t\tORDER BY\n" +
                "\t\t\t`create_time` DESC\n" +
                "\t\tLIMIT 10000000000\n" +
                "\t) t\n" +
                "GROUP BY\n" +
                "\tt.CREATE_USER_ID,t.customer_id) as t_wrt_customer_visit\n" +
                "LEFT JOIN t_wrt_customer ON t_wrt_customer.ID=t_wrt_customer_visit.CUSTOMER_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_wrt_customer_visit.CREATE_USER_ID ='" + userId + "'\n");
        if (type == 0) {
            sql.append("AND t_wrt_customer_visit.BUY_DATE = '一月内'\n");
        } else {
            sql.append("AND t_wrt_customer_visit.BUY_DATE = '一月以上'\n");
        }
        sql.append("AND LEFT (\n" +
                "\tt_wrt_customer_visit.REPORT_DATE,\n" +
                "\t7\n" +
                ") = '" + dateTime + "'\n" +
                "order by t_wrt_customer_visit.ARRIVALS_TIME desc\n");
        super.appendPageSql(sql, requestPage, pageSize);

        return super.queryForJsonList(sql.toString());
    }

    public Integer getIntentCustomerForMonthPageCount(String userId, String dateTime, Integer type) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT count(1) as count from( SELECT\n" +
                "\tt_wrt_customer.`NAME` AS `customerName`,\n" +
                "\tt_wrt_customer.ID AS customerId,\n" +
                "\tt_wrt_customer.TEL AS customerTel,\n" +
                "\tt_wrt_customer.WECHAT AS customerWechat,\n" +
                "\tt_wrt_customer_visit.ENTOURAGE AS entourageName,\n" +
                "\tt_wrt_customer_visit.ENTOURAGE_ID AS entourageId,\n" +
                "\tt_wrt_customer_visit.CUSTOMER_TYPE AS customerType,\n" +
                "\tt_wrt_customer_visit.CUSTOMER_CREDIT AS customerCredit,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_TIME AS arrivalsTime,\n" +
                "\tt_wrt_customer_visit.LEAVE_TIME AS leaveTime,\n" +
                "\tt_wrt_customer_visit.LEAVE_IMG AS leaveImg,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_IMG AS arrivalsImg,\n" +
                "\tt_wrt_customer_visit.OTHER_CONDITION AS otherCondition,\n" +
                "\tIFNULL(\n" +
                "\t\tCEIL(\n" +
                "\t\t\tCEILING(\n" +
                "\t\t\t\tUNIX_TIMESTAMP(\n" +
                "\t\t\t\t\tt_wrt_customer_visit.LEAVE_TIME\n" +
                "\t\t\t\t) - UNIX_TIMESTAMP(\n" +
                "\t\t\t\t\tt_wrt_customer_visit.ARRIVALS_TIME\n" +
                "\t\t\t\t)\n" +
                "\t\t\t) / 60\n" +
                "\t\t),\n" +
                "\t\t0\n" +
                "\t) AS time\n" +
                "FROM\n" +
                "(SELECT\n" +
                "\tt.*\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\t*\n" +
                "\t\tFROM\n" +
                "\t\t\t`t_wrt_customer_visit`\n" +
                "\t\tWHERE\n" +
                "\t\t\tCREATE_USER_ID  ='" + userId + "'\n" +
                "AND CUSTOMER_ID IS not NULL\n" +
                "\t\tORDER BY\n" +
                "\t\t\t`create_time` DESC\n" +
                "\t\tLIMIT 10000000000\n" +
                "\t) t\n" +
                "GROUP BY\n" +
                "\tt.CREATE_USER_ID,t.customer_id) as t_wrt_customer_visit\n" +
                "LEFT JOIN t_wrt_customer ON t_wrt_customer.ID=t_wrt_customer_visit.CUSTOMER_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_wrt_customer_visit.CREATE_USER_ID ='" + userId + "'\n");
        if (type == 0) {
            sql.append("AND t_wrt_customer_visit.BUY_DATE = '一月内'\n");
        } else {
            sql.append("AND t_wrt_customer_visit.BUY_DATE = '一月以上'\n");
        }
        sql.append("AND LEFT (\n" +
                "\tt_wrt_customer_visit.REPORT_DATE,\n" +
                "\t7\n" +
                ") = '" + dateTime + "'\n" +
                "order by t_wrt_customer_visit.ARRIVALS_TIME desc  ) as num\n");
        String count = super.queryForString(sql.toString(), new Object[]{});
        return Integer.parseInt(count);

    }

    public List<JSONObject> getEmailByUserId(String userIds, String dateTime, String userId, String realUserId, String regionId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "sysRegion.regionName,\n" +
                "\tsysRegion.regionId,\n" +
                "\tsysUser.userId,\n" +
                "\tsysUser.userName,\n" +
                "\temail.WId,\n" +
                "\temail.emailId,\n" +
                "\tCASE\n" +
                "WHEN email.READER = '1' THEN\n" +
                "\t'已读'\n" +
                "ELSE\n" +
                "\t'未读'\n" +
                "END AS reader,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\temail.TITLE IS NOT NULL || email.TITLE != ''\n" +
                ") THEN\n" +
                "\t'已发送'\n" +
                "ELSE\n" +
                "\t'未发送'\n" +
                "END AS titleSend,\n" +
                "  CASE\n" +
                "WHEN (\n" +
                "\tt_sys_leave.id IS NOT NULL \n" +
                ") THEN\n" +
                "\t'请假'\n" +
                "WHEN (\n" +
                "\tt_sys_sign_in.id IS  NULL \n" +
                ") THEN\n" +
                "\t'缺勤'\n" +
                "ELSE\n" +
                "\t'出勤'\n" +
                "END AS leaveDate,\n" +
                " IFNULL(emailNum.replyNum, '0') AS replyNum\n" +
                "FROM\n" +
                "\t(\n" +
                "SELECT\n" +
                "\t\t\tt_sys_region.`NAME` AS regionName,\n" +
                "\t\t\tt_sys_region.`ID` AS regionId,\n" +
                "\t\t\tt_sys_region.SORT_NO AS regionSortNo,\n" +
                "\t\t\tt_sys_company.SORT_NO AS companySortNo,\n" +
                "\t\t\tt_sys_region.COMPANY_ID AS companyId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user_2_region\n" +
                "\t\tLEFT JOIN t_sys_region ON t_sys_region.ID = t_sys_user_2_region.REGION_ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_region.COMPANY_ID\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_sys_region.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_region.PARENT_ID != 0\n" +
                "\t\tAND t_sys_user_2_region.USER_ID = '" + userId + "'\n" +
                "\t) AS sysRegion \n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "t_sys_user.`NAME` as userName,\n" +
                "\t\tt_sys_user_2_region.USER_ID AS userId,\n" +
                "\t\tt_sys_user_2_region.REGION_ID AS regionId\n" +
                "\tFROM\n" +
                "\t\tt_sys_user_2_region\n" +
                "\tLEFT JOIN t_sys_region ON t_sys_region.ID = t_sys_user_2_region.REGION_ID\n" +
                "\t\tLEFT JOIN t_sys_user ON t_sys_user.ID = t_sys_user_2_region.USER_ID\n" +
                "\tWHERE 1=1\n" +
                "\tAND t_sys_region.DEL_FLAG = 0\n" +
                "\tAND t_sys_region.PARENT_ID != 0\n" +
                "\tAND t_sys_user_2_region.USER_ID IN (" + userIds + ")\n" +
                ") AS sysUser ON sysUser.regionId = sysRegion.regionId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_email.CREATE_USER_ID AS senderId,\n" +
                "\t\tt_sys_email_receiver.USER_ID AS receiverId,\n" +
                "\t\tt_wrt_main.ID AS id,\n" +
                "\t\tt_sys_email_receiver.READER,\n" +
                "\t\tt_wrt_main.TITLE,\n" +
                "\t\tt_sys_email.ID AS emailId,\n" +
                "\t\tt_sys_email.DATA_ID AS WId\n" +
                "\tFROM\n" +
                "\t\tt_sys_email_receiver\n" +
                "\tLEFT JOIN t_sys_email ON t_sys_email_receiver.EMAIL_ID = t_sys_email.ID\n" +
                "\tLEFT JOIN t_wrt_main ON t_wrt_main.ID = t_sys_email.DATA_ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_wrt_main.flage !=- 1\n" +
                "\tAND t_sys_email.E_TYPE = 1\n" +
                "\tAND t_sys_email_receiver.USER_ID ='" + userId + "'\n" +
                "\tAND t_sys_email.CREATE_USER_ID IN (" + userIds + ")\n" +
                "\tAND t_sys_email.REPORT_DATE = '" + dateTime + "'\n" +
                ") AS email ON email.senderId = sysUser.userId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tCOUNT(1) AS replyNum,\n" +
                "\t\tDATA_ID\n" +
                "\tFROM\n" +
                "\t\tt_sys_email_receiver\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND REPLY_TIME IS NOT NULL\n" +
                "\tAND DATA_ID IN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_wrt_main.ID\n" +
                "\t\tFROM\n" +
                "\t\t\tt_wrt_main\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_wrt_main.USER_ID IN (" + userIds + ")\n" +
                "\t\tAND t_sys_email_receiver.USER_ID IN (\n" +
                "\t\t\t'" + userId + "'\n" +
                "\t\t)\n" +
                "\t\tAND t_wrt_main.REPORT_DATE = '" + dateTime + "'\n" +
                "\t)\n" +
                "\tGROUP BY\n" +
                "\t\tDATA_ID\n" +
                ") AS emailNum ON email.WId = emailNum.DATA_ID\n" +
                "LEFT JOIN t_sys_leave ON sysUser.userId = t_sys_leave.USER_ID\n" +
                "AND LEAVE_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_sign_in ON sysUser.userId = t_sys_sign_in.USER_ID\n" +
                "AND SIGN_DATE = '" + dateTime + "'\n" +
                "ORDER BY\n" +
                "\tsysRegion.companySortNo,\n" +
                "\tsysRegion.regionSortNo,\n" +
                "sysUser.userId");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getSalesAwardAmount(String userId, String dateTime, Integer type) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT t_sys_bonus.`DAY` as day,\n");
        if (type == 1) {
            sql.append("t_sys_bonus.SALE_REWARD as money,\n");
        } else {
            sql.append("t_sys_bonus.SALE_PUNISHMENT as money,\n");
        }
        sql.append("t_sys_bonus.REMARK as remark\n" +
                "FROM t_sys_bonus\n" +
                "WHERE 1=1\n" +
                "AND t_sys_bonus.TYPE=1\n" +
                "AND left(t_sys_bonus.`DAY`,7) = '" + dateTime + "'\n" +
                "AND USER_ID ='" + userId + "'\n");
        if (type == 0) {
            sql.append(" AND t_sys_bonus.SALE_PUNISHMENT is not null\n");
        } else {
            sql.append("AND t_sys_bonus.SALE_REWARD is not null\n");
        }
        sql.append("ORDER BY t_sys_bonus.DAY DESC\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getRegionManagerByUserIds(String userIds, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT v_sys_user.id as userId\n" +
                "FROM v_sys_user\n" +
                "LEFT JOIN t_sys_user_leave ON t_sys_user_leave.USER_ID = v_sys_user.id\n" +
                "WHERE 1=1\n" +
                " AND roleName in ('区域经理')\n");
        sql.append("\tAND v_sys_user.id in (" + userIds + ")\n" +
                "AND (v_sys_user.jobState!=1 ");
        if (null != dateTime) {
            sql.append("OR t_sys_user_leave.LEAVE_DATE='" + dateTime + "'");
        }
        sql.append(")\n" +
                "GROUP BY v_sys_user.id\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> regionAndCompanyByUserId(String userId, String companyIds) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_sys_company.ID AS companyId,\n" +
                "\tt_sys_company.`NAME` AS companyName,\n" +
                "\tt_sys_user_2_region.REGION_ID AS regionId,\n" +
                "\tt_sys_region.`NAME` AS regionName,\n" +
                "\tt_sys_company.SORT_NO as companySortNo," +
                "\tt_sys_region.SORT_NO as regionSortNo\n" +
                "FROM\n" +
                "\tt_sys_user_2_region\n" +
                "LEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID = t_sys_region.ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_region.company_ID = t_sys_company.ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_region.DEL_FLAG = 0\n" +
                "AND t_sys_region.PARENT_ID = 0\n" +
                "AND t_sys_company.DEL_FLAG = 0\n" +
                "AND t_sys_user_2_region.USER_ID='" + userId + "'\n" +
                "AND t_sys_region.company_ID in (" + companyIds + ")\n" +
                "GROUP BY t_sys_company.id,t_sys_region.ID\n" +
                "ORDER BY\n" +
                "\t\tt_sys_company.SORT_NO,t_sys_region.SORT_NO\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getUserIdForregionId(String userIds, String regionId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_sys_user_2_region.USER_ID AS userId\n" +
                "FROM\n" +
                "\tt_sys_user_2_region\n" +
                "LEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID = t_sys_region.ID\n" +
                "LEFT JOIN v_sys_user ON v_sys_user.id = t_sys_user_2_region.USER_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND v_sys_user.jobState != 1\n" +
                "AND v_sys_user.roleName = '大区经理'\n" +
                "AND t_sys_region.DEL_FLAG = 0\n" +
                "AND t_sys_region.`NAME`=v_sys_user.departName\n\n" +
                "AND t_sys_user_2_region.USER_ID IN (" + userIds + ")\n");
        if (!StringUtils.isBlank(regionId)) {
            sql.append("AND t_sys_user_2_region.REGION_ID = '" + regionId + "'\n");
        }
        sql.append("GROUP BY\n" +
                "\tt_sys_user_2_region.USER_ID\n");
        return super.queryForJsonList(sql.toString());
    }

    public Integer getSendEmail(String userId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tcount(1) AS count\n" +
                "FROM\n" +
                "\tt_sys_email\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND REPORT_DATE = '" + dateTime + "'\n" +
                "AND t_sys_email.CREATE_USER_ID = '" + userId + "'\n" +
                "AND t_sys_email.E_TYPE = 1\n");
        String count = super.queryForString(sql.toString(), new Object[]{});
        return Integer.parseInt(count);
    }
}